using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using HMS.BL;
using HMS.Common;
using System.Data.SqlClient;
using System.Web.Script.Serialization;
using System.Configuration;
using System.Data;
using System.Data.Common;
using HMS.Logging;
using System.Xml;
using System.Xml.Serialization;
using System.IO;

namespace HMS.DL
{

    public class RelIds
    {
        public Int32 RelId { get; set; }
    }

    public class Columns
    {
        public string colname { get; set; }
        public string colvalue { get; set; }

        public Columns()  
        {           
        }

        public Columns(string colname, string colvalue)
        {
            // TODO: Complete member initialization
            this.colname= colname;
            this.colvalue = colvalue;
        }
    }

    public class Dimensions
    {
        public string dimname { get; set; }
        public string dimabbr { get; set; }
    }

    public class NodeData
    {

        public const string ClsName = "$NodeData$";
        public int NodeId { get; set; }
        public int ParentId { get; set; }
        public int HierarchyId { get; set; }
        public int NodeTypeId { get; set; }
        public bool IsRoot { get; set; }
        public string TableName { get; set; }
        public string RelTableName { get; set; }

        public Int32 VersionId { get; set; }
        public DateTime AsOfDate { get; set; }
        public string AttrVal { get; set; }
        public string NewNodeName { get; set; }
        public string NewNodeTypeId { get; set; }
        public bool IsNodeType { get; set; }

        public int RefNodeId { get; set; }
        public bool IsCopy { get; set; }
        public DateTime EndDate { get; set; }
        public int newNodeId { get; set; }
        public DateTime txtDate { get; set; }

        public string movedNodeOldNewId { get; set; }
        public char bsave { get; set; }
        public string dimname { get; set; }
        public string dimTableName { get; set; }

        public string[] arrRelId { get; set; }
        public string queryObj { get; set; }
        public Boolean bCurrRule { get; set; }
        public Boolean bmoveRelflag { get; set; }

        public string sidx { get; set; }
        public string sord { get; set; }
        public int rows { get; set; }
        public int page { get; set; }
        public int limit { get; set; }
        public string query { get; set; }
        public string queryRel { get; set; }
        public bool _search { get; set; }
        public List<RelIds> LstRelIds { get; set; }

        public string GetAttributes()
        {
            try
            {

                string strColname = string.Empty;

                Util.WritInfoLog(ClsName, "GetAttributes", "Start Creating Connection");
                Connection sqlConn = new Connection();
                Util.WritInfoLog(ClsName, "GetAttributes", "End Creating Connection");

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@txtDate", DbType.DateTime, ParameterDirection.Input, 0, txtDate));
                lstparam.Add(new DBParameter("@HierarchyId", DbType.Int32, ParameterDirection.Input, 0, HierarchyId));
                lstparam.Add(new DBParameter("@VersionId", DbType.Int32, ParameterDirection.Input, 0, VersionId));
                lstparam.Add(new DBParameter("@NodeTypeId", DbType.Int32, ParameterDirection.Input, 0, NodeTypeId));
                lstparam.Add(new DBParameter("@TableName", DbType.String, ParameterDirection.Input, 0, TableName));
                lstparam.Add(new DBParameter("@NodeId", DbType.Int32, ParameterDirection.Input, 0, NodeId));

                Util.WritInfoLog(ClsName, "GetAttributes", "method of GetAttributes", string.Format("method:{0}", "usp_GetAttributes"));
                DataSet ds = sqlConn.GetDataSetFromFactoryProc("usp_GetAttributes", ref(lstparam));
                Util.WritInfoLog(ClsName, "GetAttributes", "End method of GetAttributes");

                //DataTable DtAttr = dsAttr.Tables[0];
                DataTable DtAttr = ds.Tables[3];

                List<NodeAttribute> listNodeAttr = new List<NodeAttribute>();

                foreach (DataRow dr in ds.Tables[0].Rows)
                {

                    NodeAttribute nodeAttr = new NodeAttribute();
                    //original
                    nodeAttr.dispalyName = dr["ATTR_NAME"].ToString();
                    nodeAttr.name = dr["ATTR_COLUMN"].ToString();

                    if (DtAttr.Rows.Count > 0)
                    {
                        nodeAttr.value = DtAttr.Rows[0][dr["ATTR_COLUMN"].ToString()].ToString();
                    }

                    nodeAttr.is_req = dr["IS_REQUIRED"].ToString();
                    nodeAttr.datatype = dr["ATTR_DATA_TYPE"].ToString().ToUpper();
                    nodeAttr.prec = dr["ATTR_PRECISION"].ToString();
                    nodeAttr.control_type = dr["CONTROL_TYPE"].ToString();

                    //for control type dropdown
                    if (dr["DROPDOWN_VAL"].ToString() != "")
                    {

                        List<NodeAttrDropVal> listnodeAttrDrpval = new List<NodeAttrDropVal>();
                        string[] arrDrpval = dr["DROPDOWN_VAL"].ToString().Split(',');
                        //add default select 
                        NodeAttrDropVal nodeAttrDrpvald = new NodeAttrDropVal();
                        nodeAttrDrpvald.text = "--select--";
                        nodeAttrDrpvald.optvalue = "";
                        listnodeAttrDrpval.Add(nodeAttrDrpvald);

                        for (int i = 0; i < arrDrpval.Length; i++)
                        {
                            NodeAttrDropVal nodeAttrDrpval = new NodeAttrDropVal();
                            nodeAttrDrpval.text = arrDrpval[i];
                            nodeAttrDrpval.optvalue = arrDrpval[i];
                            if (arrDrpval[i] == nodeAttr.value)
                            {
                                nodeAttrDrpval.selected = "true";
                            }
                            else
                            {
                                nodeAttrDrpval.selected = "false";
                            }

                            listnodeAttrDrpval.Add(nodeAttrDrpval);
                        }

                        nodeAttr.NodeAttrDropVal = listnodeAttrDrpval;
                    }

                    listNodeAttr.Add(nodeAttr);
                }

                NodeDetail nodeDetail = new NodeDetail(); //this is to be continued);

                nodeDetail.NodeName = ds.Tables[2].Rows[0]["NODE_NAME"].ToString();//
                nodeDetail.NodeTypeName = ds.Tables[1].Rows[0]["NODE_TYPE_NAME"].ToString();
                nodeDetail.UniqueId = Convert.ToInt32(ds.Tables[2].Rows[0]["UNQ_NODE_ID"]);
                nodeDetail.StartDate = ds.Tables[2].Rows[0]["START_DATE"].ToString();
                nodeDetail.EndDate = ds.Tables[2].Rows[0]["END_DATE"].ToString();
                nodeDetail.ImageUrl = Convert.ToString(ds.Tables[1].Rows[0]["IMG_URL"]);
                nodeDetail.NodeAttributes = listNodeAttr;

                JavaScriptSerializer js = new JavaScriptSerializer();
                return js.Serialize(nodeDetail);

            }
            catch (SqlException exs)
            {
                Util.WritErrorLog(ClsName, "GetAttributes", "SQL Exceptions Error in GetAttributes", "", exs);
                throw exs;
            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "GetAttributes", "Error in GetAttributes", "", ex);
                throw ex;
            }
        }

        //before SP
        public string GetAttributes1()
        {
            try
            {

                string strColname = string.Empty;

                Util.WritInfoLog(ClsName, "GetAttributes", "Start Creating Connection");
                Connection sqlConn = new Connection();
                Util.WritInfoLog(ClsName, "GetAttributes", "End Creating Connection");

                string strQry = " SELECT ATTR_NAME,ATTR_COLUMN,ATTR_DATA_TYPE,ATTR_PRECISION,IS_REQUIRED,CONTROL_TYPE,";
                strQry = strQry + " DROPDOWN_VAL FROM X_NODE_TYPE_ATTR WHERE ";
                strQry = strQry + " NODE_TYPE_ID =" + NodeTypeId + " AND HIERARCHY_ID = " + HierarchyId;

                strQry = strQry + " SELECT ATTR_TABLE_NAME,NODE_TYPE_NAME,IMG_URL FROM X_HIER_NODE_TYPES WHERE ";
                strQry = strQry + " HIERARCHY_ID= " + HierarchyId.ToString();
                strQry = strQry + " AND  NODE_TYPE_ID =" + NodeTypeId;

                strQry = strQry + " SELECT NODE_NAME,UNQ_NODE_ID,CONVERT(VARCHAR,START_DATE,101)AS START_DATE,";
                strQry = strQry + " CONVERT(VARCHAR,END_DATE,101) AS END_DATE FROM  " + TableName;
                strQry = strQry + " WHERE HIERARCHY_ID= " + HierarchyId;
                strQry = strQry + " AND NODE_ID =" + NodeId;

                Util.WritInfoLog(ClsName, "GetAttributes", "Query to be executed for getting attribute table and columns", string.Format("Query:{0}", strQry));
                DataSet ds = sqlConn.GetDataSetFromFactory(strQry);
                Util.WritInfoLog(ClsName, "GetAttributes", "End Query for getting attribute table and columns");

                //get table name of attributes
                string strTableName = string.Empty;
                if (ds.Tables[1].Rows.Count > 0)
                {
                    strTableName = ds.Tables[1].Rows[0]["ATTR_TABLE_NAME"].ToString();
                }

                //get attribute colname
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    if (strColname == "") { strColname = dr["ATTR_COLUMN"].ToString(); } else { strColname = strColname + "," + dr["ATTR_COLUMN"].ToString(); }
                }

                //get attributes value
                string strQryAttr = " SELECT TOP 1 " + strColname + " FROM " + strTableName + " WHERE NODE_ID = " + NodeId;
                strQryAttr = strQryAttr + " AND CONVERT(DATE,START_DATE,101) <= '" + txtDate.ToString("MM/dd/yyyy") + "'";
                strQryAttr = strQryAttr + " AND CONVERT(DATE,END_DATE,101) >= '" + txtDate.ToString("MM/dd/yyyy") + "'";

                strQryAttr = strQryAttr + " ORDER BY START_DATE DESC ";

                Util.WritInfoLog(ClsName, "GetAttributes", "Query to be executed for getting attributes values", string.Format("Query:{0}", strQryAttr));
                DataSet dsAttr = sqlConn.GetDataSetFromFactory(strQryAttr);
                Util.WritInfoLog(ClsName, "GetAttributes", "End Query for getting attributes values");

                DataTable DtAttr = dsAttr.Tables[0];
                List<NodeAttribute> listNodeAttr = new List<NodeAttribute>();

                foreach (DataRow dr in ds.Tables[0].Rows)
                {

                    NodeAttribute nodeAttr = new NodeAttribute();
                    nodeAttr.name = dr["ATTR_NAME"].ToString();
                    if (DtAttr.Rows.Count > 0)
                    {
                        nodeAttr.value = DtAttr.Rows[0][dr["ATTR_COLUMN"].ToString()].ToString();
                    }

                    nodeAttr.is_req = dr["IS_REQUIRED"].ToString();
                    nodeAttr.datatype = dr["ATTR_DATA_TYPE"].ToString();
                    nodeAttr.prec = dr["ATTR_PRECISION"].ToString();
                    nodeAttr.control_type = dr["CONTROL_TYPE"].ToString();

                    //for control type dropdown
                    if (dr["DROPDOWN_VAL"].ToString() != "")
                    {

                        List<NodeAttrDropVal> listnodeAttrDrpval = new List<NodeAttrDropVal>();
                        string[] arrDrpval = dr["DROPDOWN_VAL"].ToString().Split(',');
                        //add default select 
                        NodeAttrDropVal nodeAttrDrpvald = new NodeAttrDropVal();
                        nodeAttrDrpvald.text = "--select--";
                        nodeAttrDrpvald.optvalue = "";
                        listnodeAttrDrpval.Add(nodeAttrDrpvald);

                        for (int i = 0; i < arrDrpval.Length; i++)
                        {
                            NodeAttrDropVal nodeAttrDrpval = new NodeAttrDropVal();
                            nodeAttrDrpval.text = arrDrpval[i];
                            nodeAttrDrpval.optvalue = arrDrpval[i];
                            if (arrDrpval[i] == nodeAttr.value)
                            {
                                nodeAttrDrpval.selected = "true";
                            }
                            else
                            {
                                nodeAttrDrpval.selected = "false";
                            }

                            listnodeAttrDrpval.Add(nodeAttrDrpval);
                        }

                        nodeAttr.NodeAttrDropVal = listnodeAttrDrpval;
                    }

                    listNodeAttr.Add(nodeAttr);
                }

                NodeDetail nodeDetail = new NodeDetail(); //this is to be continued);

                nodeDetail.NodeName = ds.Tables[2].Rows[0]["NODE_NAME"].ToString();//
                nodeDetail.NodeTypeName = ds.Tables[1].Rows[0]["NODE_TYPE_NAME"].ToString();
                nodeDetail.UniqueId = Convert.ToInt32(ds.Tables[2].Rows[0]["UNQ_NODE_ID"]);
                nodeDetail.StartDate = ds.Tables[2].Rows[0]["START_DATE"].ToString();
                nodeDetail.EndDate = ds.Tables[2].Rows[0]["END_DATE"].ToString();
                nodeDetail.ImageUrl = Convert.ToString(ds.Tables[1].Rows[0]["IMG_URL"]);
                nodeDetail.NodeAttributes = listNodeAttr;

                JavaScriptSerializer js = new JavaScriptSerializer();
                return js.Serialize(nodeDetail);

            }
            catch (SqlException exs)
            {
                Util.WritErrorLog(ClsName, "GetAttributes", "SQL Exceptions Error in GetAttributes", "", exs);
                throw exs;
            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "GetAttributes", "Error in GetAttributes", "", ex);
                throw ex;
            }
        }

        public string GetChildren()
        {
            try
            {

                Util.WritInfoLog(ClsName, "GetChildren", "Start Creating Connection");
                Connection sqlConn = new Connection();
                Util.WritInfoLog(ClsName, "GetChildren", "End Creating Connection");

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@txtDate", DbType.DateTime, ParameterDirection.Input, 0, txtDate));
                lstparam.Add(new DBParameter("@IsRoot", DbType.Boolean, ParameterDirection.Input, 0, IsRoot));
                lstparam.Add(new DBParameter("@ParentId", DbType.Int32, ParameterDirection.Input, 0, ParentId));
                lstparam.Add(new DBParameter("@TableName", DbType.String, ParameterDirection.Input, 0, TableName));

                Util.WritInfoLog(ClsName, "GetChildren", "method of GetChildren", string.Format("method:{0}", "usp_GetChildren"));
                DataSet ds = sqlConn.GetDataSetFromFactoryProc("usp_GetChildren", ref(lstparam));
                Util.WritInfoLog(ClsName, "GetChildren", "End method of GetChildren");

                List<Node> listNodes = new List<Node>();

                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    NodeProperty nodeProperty = new NodeProperty(dr["NODE_ID"].ToString(), dr["NODE_TYPE_NAME"].ToString(), dr["NODE_TYPE_ID"].ToString(), dr["IS_LEAF"].ToString(), dr["ValidParent"].ToString(), dr["HAS_DATA"].ToString());
                    Node node = new Node(dr["NODE_NAME"].ToString(), "closed", nodeProperty);
                    listNodes.Add(node);
                }

                JavaScriptSerializer js = new JavaScriptSerializer();
                return js.Serialize(listNodes);

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "GetChildren", "Error in GetChildren", "", ex);
                throw ex;
            }
        }


        //before SP
        //public string GetChildren()
        //{
        //    try
        //    {

        //        Util.WritInfoLog(ClsName, "GetChildren", "Start Creating Connection");
        //        Connection sqlConn = new Connection();
        //        Util.WritInfoLog(ClsName, "GetChildren", "End Creating Connection");

        //        string strWhere = "";

        //        if (IsRoot == true)
        //        {
        //            strWhere = " WHERE PARENT_ID IS NULL AND CONVERT(DATE,ND.END_DATE,101) >=  '" + txtDate + "'";
        //            strWhere = strWhere + " AND  CONVERT(DATE,ND.START_DATE,101) <= '" + txtDate + "'";
        //        }
        //        else
        //        {
        //            strWhere = " WHERE PARENT_ID = " + ParentId.ToString() + " AND CONVERT(DATE,ND.END_DATE,101) >= '" + txtDate + "'";
        //            strWhere = strWhere + " AND  CONVERT(DATE,ND.START_DATE,101) <= '" + txtDate + "'";
        //        }

        //        string strJoin = " INNER JOIN X_HIER_NODE_TYPES NT ON ND.NODE_TYPE_ID = NT.NODE_TYPE_ID AND ND.HIERARCHY_ID ";
        //        strJoin = strJoin + "   = NT.HIERARCHY_ID ";

        //        string strQry = " SELECT ND.NODE_NAME, ND.NODE_ID, ND.NODE_TYPE_ID, NT.NODE_TYPE_NAME ,";

        //        //original changed for is_leaf
        //        strQry = strQry + " (SELECT CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END ";
        //        strQry = strQry + "  FROM " + TableName + " TMP WHERE TMP.PARENT_ID = ND.NODE_ID ";
        //        strQry = strQry + ") AS HAS_DATA,";

        //        strQry = strQry + " (SELECT CASE WHEN COUNT(*) > 0 THEN 0 ELSE 1 END ";
        //        strQry = strQry + "  FROM X_HIER_NODE_TYPE_PARENT TMP WHERE TMP.PARENT_NODE_TYPE_ID = ND.NODE_TYPE_ID ";
        //        strQry = strQry + " and	TMP.HIERARCHY_ID = ND.HIERARCHY_ID ) AS IS_LEAF,";

        //        //strQry = strQry + " AND CONVERT(VARCHAR,TMP.[END_DATE],101) >= '" + txtDate.ToString("MM/dd/yyyy") + "' AND CONVERT(VARCHAR,TMP.[START_DATE],101) <= '";
        //        //strQry = strQry + txtDate.ToString("MM/dd/yyyy") + "') AS IS_LEAF,";
        //        strQry = strQry + " dbo.Get_validParent(ND.NODE_TYPE_ID,NT.HIERARCHY_ID) AS ValidParent FROM ";
        //        strQry = strQry + TableName + " ND " + strJoin + strWhere;

        //        Util.WritInfoLog(ClsName, "GetChildren", "Query for GetChildren", string.Format("Query:{0}", strQry));
        //        DataSet ds = sqlConn.GetDataSetFromFactory(strQry);
        //        Util.WritInfoLog(ClsName, "GetChildren", "End Query for GetChildren");

        //        List<Node> listNodes = new List<Node>();
        //        foreach (DataRow dr in ds.Tables[0].Rows)
        //        {
        //            NodeProperty nodeProperty = new NodeProperty(dr["NODE_ID"].ToString(), dr["NODE_TYPE_NAME"].ToString(), dr["NODE_TYPE_ID"].ToString(), dr["IS_LEAF"].ToString(), dr["ValidParent"].ToString(), dr["HAS_DATA"].ToString());
        //            Node node = new Node(dr["NODE_NAME"].ToString(), "closed", nodeProperty);
        //            listNodes.Add(node);
        //        }

        //        JavaScriptSerializer js = new JavaScriptSerializer();
        //        return js.Serialize(listNodes);

        //    }
        //    catch (Exception ex)
        //    {
        //        Util.WritErrorLog(ClsName, "GetChildren", "Error in GetChildren", "", ex);
        //        throw ex;
        //    }
        //}

        public string GetTypes()
        {
            try
            {

                Util.WritInfoLog(ClsName, "GetTypes", "Start Creating Connection");
                Connection sqlConn = new Connection();
                Util.WritInfoLog(ClsName, "GetTypes", "End Creating Connection");

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@HierarchyId", DbType.Int32, ParameterDirection.Input, 0, HierarchyId));
                lstparam.Add(new DBParameter("@VersionId", DbType.Int32, ParameterDirection.Input, 0, VersionId));

                Util.WritInfoLog(ClsName, "GetTypes", "method of GetTypes", string.Format("method:{0}", "usp_GetNodeType"));
                DataSet ds = sqlConn.GetDataSetFromFactoryProc("usp_GetNodeType", ref(lstparam));
                Util.WritInfoLog(ClsName, "GetTypes", "End method of GetTypes");

                Dictionary<string, NodeType> dicTypes = new Dictionary<string, NodeType>();

                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    List<string> lstChilds = null;
                    if (!IsNodeType)
                    {
                        string[] arr = dr["ValidChild"].ToString().Split(',');
                        lstChilds = new List<string>(arr);
                    }

                    dicTypes.Add(dr["NODE_TYPE_NAME"].ToString(), new NodeType(lstChilds, new icon(dr["IMG_URL"].ToString())));
                }

                JavaScriptSerializer js = new JavaScriptSerializer();
                return js.Serialize(dicTypes);
            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "GetTypes", "Error in GetTypes", "", ex);
                throw ex;
            }
        }


        //before SP
        //public string GetTypes()
        //{
        //    try
        //    {

        //        Util.WritInfoLog(ClsName, "GetTypes", "Start Creating Connection");
        //        Connection sqlConn = new Connection();
        //        Util.WritInfoLog(ClsName, "GetTypes", "End Creating Connection");

        //        string strQry = "SELECT DISTINCT NODE_TYPE_NAME, IMG_URL,dbo.Get_validChild(TY.NODE_TYPE_ID,TY.HIERARCHY_ID) AS ValidChild";
        //        strQry = strQry + " FROM X_HIER_NODE_TYPES TY INNER JOIN X_HIER_NODE_TYPE_PARENT PA ON TY.NODE_TYPE_ID=PA.NODE_TYPE_ID";
        //        strQry = strQry + " WHERE TY.HIERARCHY_ID=" + HierarchyId.ToString();

        //        Util.WritInfoLog(ClsName, "GetTypes", "Query of GetTypes", string.Format("Query:{0}", strQry));
        //        DataSet ds = sqlConn.GetDataSetFromFactory(strQry);
        //        Util.WritInfoLog(ClsName, "GetTypes", "End Query of GetTypes");

        //        Dictionary<string, NodeType> dicTypes = new Dictionary<string, NodeType>();

        //        foreach (DataRow dr in ds.Tables[0].Rows)
        //        {

        //            string[] arr = dr["ValidChild"].ToString().Split(',');
        //            List<string> lstChilds = new List<string>(arr);

        //            dicTypes.Add(dr["NODE_TYPE_NAME"].ToString(), new NodeType(lstChilds, new icon(dr["IMG_URL"].ToString())));
        //        }

        //        JavaScriptSerializer js = new JavaScriptSerializer();
        //        return js.Serialize(dicTypes);
        //    }
        //    catch (Exception ex)
        //    {
        //        Util.WritErrorLog(ClsName, "GetTypes", "Error in GetTypes", "", ex);
        //        throw ex;
        //    }
        //}


        public string GetHierarchies()
        {
            try
            {

                Util.WritInfoLog(ClsName, "GetHierarchies", "Start Creating Connection");
                Connection sqlConn = new Connection();
                Util.WritInfoLog(ClsName, "GetHierarchies", "End Creating Connection");

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@txtDate", DbType.DateTime, ParameterDirection.Input, 0, txtDate));

                Util.WritInfoLog(ClsName, "GetHierarchies", "method of usp_GetHierarchies", string.Format("method:{0}", "usp_GetHierarchies"));
                DataSet ds = sqlConn.GetDataSetFromFactoryProc("usp_GetHierarchies", ref(lstparam));
                Util.WritInfoLog(ClsName, "GetHierarchies", "End method of usp_GetHierarchies");

                List<DropdownListItem> listHierarchies = new List<DropdownListItem>();
                listHierarchies.Add(new DropdownListItem("--Select--", ""));

                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    listHierarchies.Add(new DropdownListItem(dr["HIER_NAME"].ToString(), dr["HIERARCHY_ID"].ToString(), dr["NODE_TABLE_NAME"].ToString()));
                }

                JavaScriptSerializer js = new JavaScriptSerializer();
                return js.Serialize(listHierarchies);

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "GetHierarchies", "Error in GetHierarchies", "", ex);
                throw ex;
            }
        }

        //before SP
        //public string GetHierarchies()
        //{
        //    try
        //    {

        //        Util.WritInfoLog(ClsName, "GetHierarchies", "Start Creating Connection");
        //        Connection sqlConn = new Connection();
        //        Util.WritInfoLog(ClsName, "GetHierarchies", "End Creating Connection");

        //        //original query
        //        //string strQry = " SELECT HIER_NAME, HIERARCHY_ID, NODE_TABLE_NAME FROM X_HIERARCHIES ";

        //        string strQry = " SELECT DISTINCT HIER_NAME, HIER.HIERARCHY_ID, NODE_TABLE_NAME  ";
        //        strQry = strQry + " FROM X_HIERARCHIES HIER ";
        //        strQry = strQry + " INNER JOIN X_HIER_VERSIONS VER ";
        //        strQry = strQry + " ON HIER.HIERARCHY_ID=VER.HIERARCHY_ID ";
        //        strQry = strQry + " AND CONVERT(DATE,START_DATE,101) <= '" + txtDate.ToString("MM/dd/yyyy") + "'";
        //        strQry = strQry + " AND CONVERT(DATE,END_DATE,101) >= '" + txtDate.ToString("MM/dd/yyyy") + "'";

        //        Util.WritInfoLog(ClsName, "GetHierarchies", "Query for GetHierarchies", string.Format("Query:{0}", strQry));
        //        DataSet ds = sqlConn.GetDataSetFromFactory(strQry);
        //        Util.WritInfoLog(ClsName, "GetHierarchies", "End Query for GetHierarchies");

        //        List<DropdownListItem> listHierarchies = new List<DropdownListItem>();
        //        listHierarchies.Add(new DropdownListItem("--Select--", ""));

        //        foreach (DataRow dr in ds.Tables[0].Rows)
        //        {
        //            listHierarchies.Add(new DropdownListItem(dr["HIER_NAME"].ToString(), dr["HIERARCHY_ID"].ToString(), dr["NODE_TABLE_NAME"].ToString()));
        //        }

        //        JavaScriptSerializer js = new JavaScriptSerializer();
        //        return js.Serialize(listHierarchies);

        //    }
        //    catch (Exception ex)
        //    {
        //        Util.WritErrorLog(ClsName, "GetHierarchies", "Error in GetHierarchies", "", ex);
        //        throw ex;
        //    }
        //}


        public string GetVersions()
        {
            try
            {

                Util.WritInfoLog(ClsName, "GetVersions", "Start Creating Connection");
                Connection sqlConn = new Connection();
                Util.WritInfoLog(ClsName, "GetVersions", "End Creating Connection");

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@HierarchyId", DbType.Int32, ParameterDirection.Input, 0, HierarchyId));

                Util.WritInfoLog(ClsName, "GetVersions", "method of usp_GetVersions", string.Format("method:{0}", "usp_GetVersions"));
                DataSet ds = sqlConn.GetDataSetFromFactoryProc("usp_GetVersions", ref(lstparam));
                Util.WritInfoLog(ClsName, "GetVersions", "End method of usp_GetVersions");

                List<DropdownListItem> listVersions = new List<DropdownListItem>();
                listVersions.Add(new DropdownListItem("--Select--", ""));
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    listVersions.Add(new DropdownListItem(dr["VERSION_NAME"].ToString(), dr["VERSION_ID"].ToString(),"", dr["START_DATE"].ToString(),dr["END_DATE"].ToString()));
                }

                JavaScriptSerializer js = new JavaScriptSerializer();
                return js.Serialize(listVersions);

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "GetVersions", "Error in GetVersions", "", ex);
                throw ex;
            }
        }

        //before SP
        //public string GetVersions()
        //{
        //    try
        //    {

        //        Util.WritInfoLog(ClsName, "GetVersions", "Start Creating Connection");
        //        Connection sqlConn = new Connection();
        //        Util.WritInfoLog(ClsName, "GetVersions", "End Creating Connection");

        //        string strQry = " SELECT VERSION_ID,VERSION_NAME FROM X_HIER_VERSIONS WHERE HIERARCHY_ID=" + HierarchyId.ToString();

        //        Util.WritInfoLog(ClsName, "GetVersions", "Query for GetVersions", string.Format("Query:{0}", strQry));
        //        DataSet ds = sqlConn.GetDataSetFromFactory(strQry);
        //        Util.WritInfoLog(ClsName, "GetVersions", "End Query for GetVersions");

        //        List<DropdownListItem> listVersions = new List<DropdownListItem>();
        //        listVersions.Add(new DropdownListItem("--Select--", ""));
        //        foreach (DataRow dr in ds.Tables[0].Rows)
        //        {
        //            listVersions.Add(new DropdownListItem(dr["VERSION_NAME"].ToString(), dr["VERSION_ID"].ToString()));
        //        }

        //        JavaScriptSerializer js = new JavaScriptSerializer();
        //        return js.Serialize(listVersions);

        //    }
        //    catch (Exception ex)
        //    {
        //        Util.WritErrorLog(ClsName, "GetVersions", "Error in GetVersions", "", ex);
        //        throw ex;
        //    }
        //}

        //public string SaveAttributes()
        //{

        //    try
        //    {

        //        Connection sqlConn = new Connection();

        //        //get end date of that hier_id and ver_id  -1
        //        string strQry = " SELECT END_DATE FROM X_HIER_VERSIONS ";
        //        strQry = strQry + " WHERE HIERARCHY_ID = " + HierarchyId;
        //        strQry = strQry + " AND VERSION_ID = " + VersionId;

        //        DataSet ds = sqlConn.GetDataSetFromFactory(strQry);

        //        if (ds.Tables[0].Rows.Count > 0)
        //        {
        //            EndDate = Convert.ToDateTime(ds.Tables[0].Rows[0]["END_DATE"]);
        //        }


        //        int ret=saveAttributes(NodeId, EndDate, NodeTypeId);

        //        return "true";

        //    }
        //    catch (Exception ex)
        //    {
        //        return "false";
        //        throw ex;

        //    }
        //}

        public string SaveAttributes()
        {

            try
            {

                Util.WritInfoLog(ClsName, "SaveAttributes", "Start Creating Connection");
                Connection sqlConn = new Connection();
                Util.WritInfoLog(ClsName, "SaveAttributes", "End Creating Connection");

                JavaScriptSerializer js = new JavaScriptSerializer();
                List<Columns> listCols = js.Deserialize<List<Columns>>(AttrVal);

                var xmlDocument = new XmlDocument();
                var serializer = new XmlSerializer(typeof(List<Columns>));

                using (var stream = new MemoryStream())
                {
                    serializer.Serialize(stream, listCols);
                    stream.Flush(); stream.Seek(0, SeekOrigin.Begin);
                    xmlDocument.Load(stream);
                }

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@NodeId", DbType.Int32, ParameterDirection.Input, 0, NodeId));
                lstparam.Add(new DBParameter("@NodeTypeId", DbType.Int32, ParameterDirection.Input, 0, NodeTypeId));
                lstparam.Add(new DBParameter("@field_Xml", DbType.Xml, ParameterDirection.Input, 0, xmlDocument.OuterXml));
                lstparam.Add(new DBParameter("@HierarchyId", DbType.Int32, ParameterDirection.Input, 0, HierarchyId));
                lstparam.Add(new DBParameter("@VersionId", DbType.Int32, ParameterDirection.Input, 0, VersionId));
                lstparam.Add(new DBParameter("@TableName", DbType.String, ParameterDirection.Input, 255, TableName));

                Util.WritInfoLog(ClsName, "SaveAttributes", "method of usp_SaveAttributes", string.Format("method:{0}", "usp_SaveAttributes"));
                Int32 ret = sqlConn.SaveDataByFactoryByProc("usp_SaveAttributes", ref(lstparam));
                Util.WritInfoLog(ClsName, "SaveAttributes", "End method of usp_SaveAttributes");

                return "true";

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "SaveAttributes", "Error in SaveAttributes", "", ex);
                return "false";
                throw ex;
            }
        }


        //before SP
        //public string SaveAttributes()
        //{

        //    try
        //    {

        //        Util.WritInfoLog(ClsName, "SaveAttributes", "Start Creating Connection");
        //        Connection sqlConn = new Connection();
        //        Util.WritInfoLog(ClsName, "SaveAttributes", "End Creating Connection");

        //        //get attribute table name
        //        string strQry = " SELECT ATTR_TABLE_NAME FROM X_HIER_NODE_TYPES WHERE HIERARCHY_ID= " + HierarchyId.ToString();
        //        strQry = strQry + "AND NODE_TYPE_ID =" + NodeTypeId;

        //        //get end date of that hier_id and ver_id  -1
        //        strQry = strQry + " SELECT END_DATE FROM X_HIER_VERSIONS ";
        //        strQry = strQry + " WHERE HIERARCHY_ID = " + HierarchyId;
        //        strQry = strQry + " AND VERSION_ID = " + VersionId;

        //        Util.WritInfoLog(ClsName, "SaveAttributes", "Query for SaveAttributes for getting table and enddate", string.Format("Query:{0}", strQry));
        //        DataSet ds = sqlConn.GetDataSetFromFactory(strQry);
        //        Util.WritInfoLog(ClsName, "SaveAttributes ", "End Query for SaveAttributes for getting table and enddate");

        //        if (ds.Tables[1].Rows.Count > 0)
        //        {
        //            EndDate = Convert.ToDateTime(ds.Tables[1].Rows[0]["END_DATE"]);
        //        }

        //        string strTableName = string.Empty;
        //        if (ds.Tables[0].Rows.Count > 0)
        //        {
        //            strTableName = ds.Tables[0].Rows[0]["ATTR_TABLE_NAME"].ToString();
        //        }

        //        // to get column data type
        //        //string strQryDataType = " SELECT * FROM X_NODE_TYPE_ATTR WHERE HIERARCHY_ID=" + HierarchyId.ToString();
        //        //strQryDataType = strQryDataType + " AND NODE_TYPE_ID = (SELECT NODE_TYPE_ID FROM " + TableName + " WHERE NODE_ID = " + NodeId.ToString() + ")";

        //        string strQryDataType = " SELECT * FROM X_NODE_TYPE_ATTR WHERE HIERARCHY_ID=" + HierarchyId.ToString();
        //        strQryDataType = strQryDataType + " AND NODE_TYPE_ID = " + NodeTypeId;

        //        Util.WritInfoLog(ClsName, "SaveAttributes", "Query for Getting Attribute name of that node type", string.Format("Query:{0}", strQryDataType));
        //        DataSet dsPrec = sqlConn.GetDataSet(strQryDataType);
        //        Util.WritInfoLog(ClsName, "SaveAttributes", "End Query for Getting Attribute name of that node type");

        //        DataTable dtType = dsPrec.Tables[0];
        //        DataView DvType = new DataView(dtType);

        //        JavaScriptSerializer js = new JavaScriptSerializer();
        //        List<Columns> listCols = js.Deserialize<List<Columns>>(AttrVal);

        //        //update end_date to row which we have to save
        //        string strQryUpd = " UPDATE " + strTableName + " SET END_DATE = '" + DateTime.Now.AddDays(-1) + "'";
        //        //strQryUpd = strQryUpd + " WHERE NODE_ID = " + NodeId.ToString() + " AND END_DATE >= '" + DateTime.Now.ToString("MM/dd/yyyy") + "'";
        //        strQryUpd = strQryUpd + " WHERE START_DATE = ( SELECT TOP(1) START_DATE FROM " + strTableName;
        //        strQryUpd = strQryUpd + " WHERE NODE_ID = " + NodeId.ToString() + " AND  CONVERT(DATE,END_DATE,101) >= '" + DateTime.Now.ToString("MM/dd/yyyy") + "'";
        //        strQryUpd = strQryUpd + " ORDER BY START_DATE DESC )";

        //        Util.WritInfoLog(ClsName, "SaveAttributes", "Query for setting end date to todate to current rows", string.Format("Query:{0}", strQryUpd));
        //        Int32 ret = sqlConn.SaveDataByFactory(strQryUpd);
        //        Util.WritInfoLog(ClsName, "SaveAttributes", "End Query setting end date to todate to current rows");

        //        string strInsert = "INSERT INTO " + strTableName;

        //        string colNm = string.Empty;
        //        string colVl = string.Empty;

        //        foreach (Columns colElem in listCols)
        //        {

        //            DvType.RowFilter = "ATTR_NAME ='" + colElem.colname + "'"; //ATTR_DATA_TYPE
        //            if (DvType[0]["ATTR_DATA_TYPE"].ToString().ToUpper() == "VARCHAR" || DvType[0]["ATTR_DATA_TYPE"].ToString().ToUpper() == "DATE")
        //            {
        //                colVl = colVl + "'" + colElem.colvalue + "'" + ",";//ATTR_COLUMN
        //            }
        //            else if (DvType[0]["ATTR_DATA_TYPE"].ToString().ToUpper() == "NUMBER")
        //            {
        //                colVl = colVl + colElem.colvalue + ",";
        //            }

        //            colNm = colNm + DvType[0]["ATTR_COLUMN"].ToString() + ",";
        //        }

        //        colNm = colNm.Substring(0, colNm.Length - 1);
        //        colVl = colVl.Substring(0, colVl.Length - 1);

        //        strInsert = strInsert + "(NODE_ID,START_DATE,END_DATE," + colNm + ")";
        //        strInsert = strInsert + "VALUES (" + NodeId + ",'" + DateTime.Now + "','" + Convert.ToDateTime(EndDate).ToString("MM/dd/yyyy") + "'," + colVl + ")";

        //        Util.WritInfoLog(ClsName, "SaveAttributes", "Query for SaveAttributes for inserting new row", string.Format("Query:{0}", strInsert));
        //        Int32 ret2 = sqlConn.SaveDataByFactory(strInsert);
        //        Util.WritInfoLog(ClsName, "SaveAttributes", "End Query for SaveAttributes for inserting new row");

        //        return "true";

        //    }
        //    catch (Exception ex)
        //    {
        //        Util.WritErrorLog(ClsName, "SaveAttributes", "Error in SaveAttributes", "", ex);
        //        return "false";
        //        throw ex;
        //    }

        //}

        protected int saveAttributes(Int32 nodeid, DateTime enddate, Int32 nodetypeid, Int32 newnodeid)
        {

            try
            {

                Util.WritInfoLog(ClsName, "SaveAttributes(,)", "Start Creating Connection");
                Connection sqlConn = new Connection();
                Util.WritInfoLog(ClsName, "SaveAttributes(,)", "End Creating Connection");

                //get attribute table name
                string strQry = " SELECT ATTR_TABLE_NAME FROM X_HIER_NODE_TYPES WHERE HIERARCHY_ID=" + HierarchyId.ToString();
                strQry = strQry + "AND NODE_TYPE_ID =" + nodetypeid;

                Util.WritInfoLog(ClsName, "SaveAttributes(,)", "Query for getting ATTR_TABLE_NAME", string.Format("Query:{0}", strQry));
                DataSet ds = sqlConn.GetDataSetFromFactory(strQry);
                Util.WritInfoLog(ClsName, "SaveAttributes(,)", "End Query for getting ATTR_TABLE_NAME");

                string strTableName = string.Empty;
                if (ds.Tables[0].Rows.Count > 0)
                {
                    strTableName = ds.Tables[0].Rows[0]["ATTR_TABLE_NAME"].ToString();
                }

                // to get column data type
                string strQryDataType = " SELECT * FROM X_NODE_TYPE_ATTR WHERE HIERARCHY_ID=" + HierarchyId.ToString();
                strQryDataType = strQryDataType + " AND NODE_TYPE_ID = " + nodetypeid;

                // to get attribute value
                strQryDataType = strQryDataType + " SELECT * FROM " + strTableName;
                strQryDataType = strQryDataType + " WHERE NODE_ID = " + nodeid;
               // strQryDataType = strQryDataType + " AND END_DATE = '" + enddate + "'";
                strQryDataType = strQryDataType + " AND END_DATE = ( SELECT TOP(1) START_DATE FROM " + strTableName;
                strQryDataType = strQryDataType + " WHERE NODE_ID = " + nodeid;
                strQryDataType = strQryDataType + " ORDER BY END_DATE DESC  )";


                DataSet dsPrec = sqlConn.GetDataSet(strQryDataType);
                DataTable dtType = dsPrec.Tables[0];
                DataView DvType = new DataView(dtType);


                DataTable dtAttrval = dsPrec.Tables[1];
                DataView DvAttrval = new DataView(dtAttrval);

                if (DvAttrval.Count > 0)
                {
                    
                    //update end_date to row which we have to save
                    string strQryUpd = " UPDATE " + strTableName + " SET END_DATE = '" + DateTime.Now.AddDays(-1) + "'";
                    strQryUpd = strQryUpd + " WHERE START_DATE = ( SELECT TOP(1) START_DATE FROM " + strTableName;
                    strQryUpd = strQryUpd + " WHERE NODE_ID = " + NodeId.ToString() + " AND END_DATE >= '" + DateTime.Now.ToString("MM/dd/yyyy") + "'";
                    strQryUpd = strQryUpd + " ORDER BY START_DATE DESC )";

                    //changed by poon on 26082011 for transaction
                    Util.WritInfoLog(ClsName, "SaveAttributes(,)", "Query for setting end date on current rows", string.Format("Query:{0}", strQryUpd));
                    Int32 ret = sqlConn.SaveDataByFactory(strQryUpd);
                    Util.WritInfoLog(ClsName, "SaveAttributes(,)", "End Query for setting end date on current rows");
                    //Int32 ret = sqlConn.SaveDataByFactory(strQryUpd, dbconn, dbtrans);

                    string strInsert = "INSERT INTO " + strTableName;

                    string colNm = string.Empty;
                    string colVl = string.Empty;

                    for (int i = 0; i < DvType.Count; i++)
                    {

                        if (DvType[i]["ATTR_DATA_TYPE"].ToString().ToUpper() == "VARCHAR")
                        {
                            colVl = colVl + "'" + DvAttrval[0][DvType[i]["ATTR_COLUMN"].ToString()].ToString() + "'" + ",";//ATTR_COLUMN
                        }
                        else if (DvType[i]["ATTR_DATA_TYPE"].ToString().ToUpper() == "NUMBER")
                        {
                            colVl = colVl + DvAttrval[0][DvType[i]["ATTR_COLUMN"].ToString()].ToString() + ",";
                        }

                        colNm = colNm + DvType[i]["ATTR_COLUMN"].ToString() + ",";

                    }


                    colNm = colNm.Substring(0, colNm.Length - 1);
                    colVl = colVl.Substring(0, colVl.Length - 1);

                    strInsert = strInsert + "(NODE_ID,START_DATE,END_DATE," + colNm + ")";
                    strInsert = strInsert + "VALUES (" + newnodeid + ",'" + DateTime.Now + "','" + Convert.ToDateTime(enddate).ToString("MM/dd/yyyy") + "'," + colVl + ")";

                    //changed by poon on 26082011 for transaction
                    Util.WritInfoLog(ClsName, "SaveAttributes(,)", "Query for inserting new rows", string.Format("Query:{0}", strInsert));
                    Int32 ret2 = sqlConn.SaveDataByFactory(strInsert);
                    Util.WritInfoLog(ClsName, "SaveAttributes(,)", "End Query for inserting new rows");
                    //Int32 ret2 = sqlConn.SaveDataByFactory(strQryUpd, dbconn, dbtrans);

                    return ret2;
                }
                else
                {
                    return 0;
                }

            }
            catch (Exception ex)
            {
                //dbconn.Close();
                // dbconn.Dispose();
                // dbtrans.Dispose();
                Util.WritErrorLog(ClsName, "SaveAttributes(,,)", "Error in SaveAttributes(,,)", "", ex);
                throw ex;
            }
        }


        public string UpdateNodeName()
        {
            try
            {

                Util.WritInfoLog(ClsName, "UpdateNodeName", "Start Creating Connection");
                Connection sqlConn = new Connection();
                Util.WritInfoLog(ClsName, "UpdateNodeName", "End Creating Connection");

                //string strQry = " UPDATE " + TableName + " SET NODE_NAME = '" + NewNodeName + "' WHERE NODE_ID = " + NodeId.ToString();
                JavaScriptSerializer js = new JavaScriptSerializer();

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@NewNodeName", DbType.String, ParameterDirection.Input, 0, NewNodeName));
                lstparam.Add(new DBParameter("@TableName", DbType.String, ParameterDirection.Input, 0, TableName));
                lstparam.Add(new DBParameter("@NodeId", DbType.Int32, ParameterDirection.Input, 0, NodeId));

                Util.WritInfoLog(ClsName, "UpdateNodeName", "method of usp_UpdateNodeName", string.Format("method:{0}", "usp_UpdateNodeName"));
                Int32 ret = Convert.ToInt32(sqlConn.SaveDataByFactoryByProc("usp_UpdateNodeName", ref(lstparam)));
                Util.WritInfoLog(ClsName, "UpdateNodeName", "End method of usp_UpdateNodeName");

                return js.Serialize(new Records(ret));

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "UpdateNodeName", "UpdateNodeName", "", ex);
                throw ex;
            }
        }


        //before SP
        //public string UpdateNodeName()
        //{
        //    try
        //    {

        //        Util.WritInfoLog(ClsName, "UpdateNodeName", "Start Creating Connection");
        //        Connection sqlConn = new Connection();
        //        Util.WritInfoLog(ClsName, "UpdateNodeName", "End Creating Connection");

        //        string strQry = " UPDATE " + TableName + " SET NODE_NAME = '" + NewNodeName + "' WHERE NODE_ID = " + NodeId.ToString();
        //        JavaScriptSerializer js = new JavaScriptSerializer();

        //        Util.WritInfoLog(ClsName, "UpdateNodeName", "Query for updating node name with return statment", string.Format("Query:{0}", strQry));
        //        return js.Serialize(new Records(sqlConn.ExecuteCommand(strQry)));              

        //    }
        //    catch (Exception ex)
        //    {
        //        Util.WritErrorLog(ClsName, "UpdateNodeName", "UpdateNodeName", "", ex);
        //        throw ex;
        //    }
        //}


        public string CreateNode()
        {
            try
            {

                Util.WritInfoLog(ClsName, "CreateNode", "Start Creating Connection");
                Connection sqlConn = new Connection();
                Util.WritInfoLog(ClsName, "CreateNode", "End Creating Connection");

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@HierarchyId", DbType.Int32, ParameterDirection.Input, 0,HierarchyId));
                lstparam.Add(new DBParameter("@VersionId", DbType.Int32, ParameterDirection.Input, 0, VersionId));
                lstparam.Add(new DBParameter("@NodeTypeId", DbType.Int32, ParameterDirection.Input, 0, NodeTypeId));
                lstparam.Add(new DBParameter("@NewNodeTypeId", DbType.Int32, ParameterDirection.Input, 0, NewNodeTypeId));
                lstparam.Add(new DBParameter("@ParentId", DbType.Int32, ParameterDirection.Input, 0, ParentId));//@ParentId
                lstparam.Add(new DBParameter("@TableName", DbType.String, ParameterDirection.Input, 255, TableName));
                lstparam.Add(new DBParameter("@NewNodeName", DbType.String, ParameterDirection.Input, 255, NewNodeName));
                lstparam.Add(new DBParameter("@IDENTITY", DbType.Int32, ParameterDirection.Output, 0, 0));
                lstparam.Add(new DBParameter("@COUNT", DbType.Int32, ParameterDirection.Output,0,0));

                Util.WritInfoLog(ClsName, "CreateNode", "method for create node", "usp_CreateNode");
                Int32 res = sqlConn.SaveDataByFactoryByProc("usp_CreateNode", ref(lstparam));
                Util.WritInfoLog(ClsName, "CreateNode", "End method for create node");

                Int32 ident = 0;
                int retVal = 0;

                var iden = from param in lstparam
                           where param.name == "@IDENTITY"
                           select param.value ;

                ident = Convert.ToInt32((iden.First() is DBNull) ? 0 : iden.First());
   
                var retVal1 = from param in lstparam
                             where param.name == "@COUNT"
                             select param.value;

                retVal = Convert.ToInt32((retVal1.First() is DBNull) ? 0 : retVal1.First());
                return ident+ ":" + retVal;

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "CreateNode", "CreateNode", "", ex);
                throw ex;
            }
        }


        //before SP
        //public string CreateNode()
        //{
        //    try
        //    {

        //        Util.WritInfoLog(ClsName, "CreateNode", "Start Creating Connection");
        //        Connection sqlConn = new Connection();
        //        Util.WritInfoLog(ClsName, "CreateNode", "End Creating Connection");

        //        //get end date of that hier_id and ver_id  -0
        //        string strQrySel = " SELECT END_DATE FROM X_HIER_VERSIONS ";
        //        strQrySel = strQrySel + " WHERE HIERARCHY_ID = " + HierarchyId;
        //        strQrySel = strQrySel + " AND VERSION_ID = " + VersionId;

        //        //get max + 1  node_id -1
        //        strQrySel = strQrySel + " SELECT MAX(NODE_ID)+ 1 AS NODE_ID , MAX(UNQ_NODE_ID)+ 1 AS UNQ_NODE_ID FROM " + TableName;

        //        //get node id og new node -1
        //        // strQrySel = strQrySel + " SELECT NODE_TYPE_ID FROM " + TableName + " WHERE NODE_ID=" + NewNodeId;

        //        //get unique parent node id
        //        strQrySel = strQrySel + " SELECT UNQ_NODE_ID FROM " + TableName + " WHERE NODE_ID=" + ParentId;

        //        //get if new node is leaf
        //        strQrySel = strQrySel + " SELECT COUNT(*) AS CNT FROM X_HIER_NODE_TYPE_PARENT ";
        //        strQrySel = strQrySel + " WHERE PARENT_NODE_TYPE_ID=" + NewNodeTypeId;

        //        Util.WritInfoLog(ClsName, "CreateNode", "Query for getting details before create node", string.Format("Query:{0}", strQrySel));
        //        DataSet ds = sqlConn.GetDataSetFromFactory(strQrySel);
        //        Util.WritInfoLog(ClsName, "CreateNode", "End Query for getting details before create node");

        //        EndDate = Convert.ToDateTime(ds.Tables[0].Rows[0]["END_DATE"]);
        //        StringBuilder SB = new StringBuilder();

        //        //NewNodeTypeId
        //        //SB.Append(" INSERT INTO " + TableName + " VALUES (" + ds.Tables[1].Rows[0]["NODE_ID"] + ", " + HierarchyId.ToString() + "," + VersionId.ToString() + ", " + ds.Tables[2].Rows[0]["NODE_TYPE_ID"] + ", ");
        //        //SB.Append(ds.Tables[1].Rows[0]["UNQ_NODE_ID"] + ", '" + NewNodeName.ToString() + "', '" + DateTime.Today.ToString("MM/dd/yyyy") + "', '" + Convert.ToDateTime(EndDate).ToString("MM/dd/yyyy") + "', ");
        //        //SB.Append(" " + ParentId.ToString() + ", " + ParentId.ToString() + ") ");

        //        SB.Append(" INSERT INTO " + TableName + " VALUES (" + ds.Tables[1].Rows[0]["NODE_ID"] + ", " + HierarchyId.ToString() + "," + VersionId.ToString() + ", " + NewNodeTypeId + ", ");
        //        SB.Append(ds.Tables[1].Rows[0]["UNQ_NODE_ID"] + ", '" + NewNodeName.ToString() + "', '" + DateTime.Today.ToString("MM/dd/yyyy") + "', '" + Convert.ToDateTime(EndDate).ToString("MM/dd/yyyy") + "', ");
        //        SB.Append(" " + ParentId.ToString() + ", " + ds.Tables[2].Rows[0]["UNQ_NODE_ID"] + ") ");

        //        JavaScriptSerializer js = new JavaScriptSerializer();

        //        Util.WritInfoLog(ClsName, "CreateNode", "Insert Query for create node", string.Format("Query:{0}", SB.ToString()));
        //        int ret = sqlConn.ExecuteCommand(SB.ToString());
        //        Util.WritInfoLog(ClsName, "CreateNode", "End Query for create node");


        //        // return js.Serialize(new Records(sqlConn.ExecuteCommand(SB.ToString())));
        //        return ds.Tables[1].Rows[0]["NODE_ID"].ToString() + ":" + ds.Tables[3].Rows[0]["CNT"];

        //    }
        //    catch (Exception ex)
        //    {
        //        Util.WritErrorLog(ClsName, "CreateNode", "CreateNode", "", ex);
        //        throw ex;
        //    }
        //}

        public string CreateChild()
        {
            try
            {

                Util.WritInfoLog(ClsName, "CreateChild()", "Start Creating Connection");
                Connection sqlConn = new Connection();
                Util.WritInfoLog(ClsName, "CreateChild()", "End Creating Connection");

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@HierarchyId", DbType.Int32, ParameterDirection.Input, 0, HierarchyId));
                lstparam.Add(new DBParameter("@VersionId", DbType.Int32, ParameterDirection.Input, 0, VersionId));
                lstparam.Add(new DBParameter("@TableName", DbType.String, ParameterDirection.Input, 0, TableName));
                lstparam.Add(new DBParameter("@txtDate", DbType.DateTime, ParameterDirection.Input, 0, txtDate));
                lstparam.Add(new DBParameter("@NodeId", DbType.Int32, ParameterDirection.Input, 0, NodeId));

                Util.WritInfoLog(ClsName, "CreateChild", "method of usp_CreateChild", string.Format("method:{0}", "usp_CreateChild"));
                DataSet ds = sqlConn.GetDataSetFromFactoryProc("usp_CreateChild", ref(lstparam));
                Util.WritInfoLog(ClsName, "CreateChild", "End method of usp_CreateChild");

                ////commented by poonam to take data from configuration table on 27 aug 2011
                ////string strQry = " SELECT NODE_NAME, NODE_ID FROM " + TableName.ToString() + " WHERE PARENT_ID = " + NodeId.ToString();
                //////strQry = strQry + " AND END_DATE > '" + txtDate.ToString() + "'";
                ////strQry = strQry + " AND CONVERT(VARCHAR,START_DATE,101) <= '" + txtDate.ToString("MM/dd/yyyy") + "'";
                ////strQry = strQry + " AND CONVERT(VARCHAR,END_DATE,101) >= '" + txtDate.ToString("MM/dd/yyyy") + "'";

                List<DropdownListItem> listVersions = new List<DropdownListItem>();
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    listVersions.Add(new DropdownListItem(dr["NODE_TYPE_NAME"].ToString(), dr["NODE_TYPE_ID"].ToString()));
                }

                JavaScriptSerializer js = new JavaScriptSerializer();
                return js.Serialize(listVersions);
            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "CreateChild", "CreateChild", "", ex);
                throw ex;
            }
        }

        // before SP
        //public string CreateChild()
        //{
        //    try
        //    {

        //        Util.WritInfoLog(ClsName, "CreateChild()", "Start Creating Connection");
        //        Connection sqlConn = new Connection();
        //        Util.WritInfoLog(ClsName, "CreateChild()", "End Creating Connection");

        //        //commented by poonam to take data from configuration table on 27 aug 2011
        //        //string strQry = " SELECT NODE_NAME, NODE_ID FROM " + TableName.ToString() + " WHERE PARENT_ID = " + NodeId.ToString();
        //        ////strQry = strQry + " AND END_DATE > '" + txtDate.ToString() + "'";
        //        //strQry = strQry + " AND CONVERT(VARCHAR,START_DATE,101) <= '" + txtDate.ToString("MM/dd/yyyy") + "'";
        //        //strQry = strQry + " AND CONVERT(VARCHAR,END_DATE,101) >= '" + txtDate.ToString("MM/dd/yyyy") + "'";

        //        string strQry = " SELECT NODE_TYPE_ID FROM " + TableName.ToString() + " WHERE NODE_ID = " + NodeId.ToString();
        //        strQry = strQry + " AND  CONVERT(DATE,START_DATE,101) <= '" + txtDate + "'";
        //        strQry = strQry + " AND  CONVERT(DATE,END_DATE,101) >= '" + txtDate + "'";

        //        Util.WritInfoLog(ClsName, "CreateChild", "Query for getting node type id whose child we have to create", string.Format("Query:{0}", strQry));
        //        DataSet ds1 = sqlConn.GetDataSet(strQry);
        //        Util.WritInfoLog(ClsName, "CreateChild", "End Query for getting node type id whose child we have to create");

        //        int NodeTypeId = Convert.ToInt32(ds1.Tables[0].Rows[0]["NODE_TYPE_ID"]);

        //        strQry = " SELECT DISTINCT PA.NODE_TYPE_ID,ND.NODE_TYPE_NAME FROM X_HIER_NODE_TYPE_PARENT PA ";
        //        strQry = strQry + " INNER JOIN X_HIER_NODE_TYPES ND ON PA.NODE_TYPE_ID =ND.NODE_TYPE_ID ";
        //        strQry = strQry + " WHERE PARENT_NODE_TYPE_ID = " + NodeTypeId;
        //        strQry = strQry + " AND ND.HIERARCHY_ID =" + HierarchyId;
        //        strQry = strQry + " AND PA.HIERARCHY_ID =" + HierarchyId;
        //        //X_HIER_NODE_TYPES.HIERARCHY_ID

        //        Util.WritInfoLog(ClsName, "CreateChild", "Query for getting child nodes", string.Format("Query:{0}", strQry));
        //        DataSet ds = sqlConn.GetDataSet(strQry);
        //        Util.WritInfoLog(ClsName, "CreateChild", "End Query for getting child nodes");


        //        List<DropdownListItem> listVersions = new List<DropdownListItem>();
        //        foreach (DataRow dr in ds.Tables[0].Rows)
        //        {
        //            listVersions.Add(new DropdownListItem(dr["NODE_TYPE_NAME"].ToString(), dr["NODE_TYPE_ID"].ToString()));
        //        }

        //        JavaScriptSerializer js = new JavaScriptSerializer();
        //        return js.Serialize(listVersions);
        //    }
        //    catch (Exception ex)
        //    {
        //        Util.WritErrorLog(ClsName, "CreateChild", "CreateChild", "", ex);
        //        throw ex;
        //    }
        //}


        public string MoveNode1()
        {
            try
            {

                //validate the move
                //--bool bValidate = ValidateMove();

                //if (bValidate == true)
                //{

                    Util.WritInfoLog(ClsName, "MoveNode", "Start Creating Connection");
                    Connection sqlConn = new Connection();
                    Util.WritInfoLog(ClsName, "MoveNode", "End Creating Connection");

                    List<DBParameter> lstparam = new List<DBParameter>();
                    lstparam.Add(new DBParameter("@HierarchyId", DbType.Int32, ParameterDirection.Input, 0, HierarchyId));
                    lstparam.Add(new DBParameter("@VersionId", DbType.Int32, ParameterDirection.Input, 0, VersionId));
                    lstparam.Add(new DBParameter("@NODE_ID", DbType.Int32, ParameterDirection.Input, 0, NodeId));
                    lstparam.Add(new DBParameter("@NODE_TYPE_ID", DbType.Int32, ParameterDirection.Input, 0, NodeTypeId));
                    lstparam.Add(new DBParameter("@PARENT_ID", DbType.Int32, ParameterDirection.Input, 0, RefNodeId));                   
                    lstparam.Add(new DBParameter("@bmoveRelflag", DbType.Boolean, ParameterDirection.Input, 0, bmoveRelflag));
                    lstparam.Add(new DBParameter("@txtDate", DbType.DateTime, ParameterDirection.Input, 0, txtDate));
                    lstparam.Add(new DBParameter("@TableName", DbType.String, ParameterDirection.Input, 0, TableName));
                    lstparam.Add(new DBParameter("@RetVal", DbType.String, ParameterDirection.Output, -1));

                    Util.WritInfoLog(ClsName, "MoveNode", "method of usp_moveNode1", string.Format("method:{0}", "usp_moveNode1"));
                    Int32 ret = sqlConn.SaveDataByFactoryByProc("usp_MoveNodeNew", ref(lstparam));
                    Util.WritInfoLog(ClsName, "MoveNode", "End method of usp_moveNode1");

                    string retVal="";

                    foreach (DBParameter dbparam in lstparam)
                    {
                        if (dbparam.name == "@RetVal")
                        {
                            retVal = dbparam.value.ToString();
                            break;
                        }
                    }                  

                    if (retVal == "notvalid")
                    {
                        return "false";
                    }
                    else
                    {
                        return retVal;
                    }

                    // to do new and old node_id 

                    //return newNodeId.ToString();
                   // return movedNodeOldNewId.ToString();

                //}
                //else
                //{
                //    return "false";
                //}

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "MoveNode", "MoveNode", "", ex);
                throw ex;
            }
        }

       // before SP
        public string MoveNode()
        {
            try
            {

                //validate the move
                bool bValidate = ValidateMove();

                if (bValidate == true)
                {


                    Util.WritInfoLog(ClsName, "MoveNode", "Start Creating Connection");
                    Connection sqlConn = new Connection();
                    Util.WritInfoLog(ClsName, "MoveNode", "End Creating Connection");

                    //get end date of that hier_id and ver_id  -0
                    string strQrySel = " SELECT END_DATE FROM X_HIER_VERSIONS ";
                    strQrySel = strQrySel + " WHERE HIERARCHY_ID = " + HierarchyId;
                    strQrySel = strQrySel + " AND VERSION_ID = " + VersionId;

                    //get relation table name from X_HIER_MASTER_LIST_REL
                    strQrySel = strQrySel + " SELECT X_HIER_MASTER_LIST_REL.TABLE_NAME FROM X_HIER_MASTER_LIST_REL ";
                    strQrySel = strQrySel + " INNER JOIN X_MASTER_LIST ON ";
                    strQrySel = strQrySel + " X_HIER_MASTER_LIST_REL.MASTER_LIST_ID =X_MASTER_LIST.LIST_ID ";
                    strQrySel = strQrySel + " WHERE HIERARCHY_ID= " + HierarchyId;
                    //strQrySel = strQrySel + " AND TABLE_ABBR= '" + dimname + "'";

                    Util.WritInfoLog(ClsName, "MoveNode", "Query for getting end date and relation table name", string.Format("Query:{0}", strQrySel));
                    DataSet ds = sqlConn.GetDataSetFromFactory(strQrySel);
                    Util.WritInfoLog(ClsName, "MoveNode", "End Query for getting end date and relation table name");

                    //end date of that hier_id and ver_id
                    EndDate = Convert.ToDateTime(ds.Tables[0].Rows[0]["END_DATE"]);
                    //RelTableName = Convert.ToString(ds.Tables[1].Rows[0]["TABLE_NAME"]);

                    //get connection with transaction
                    // DbConnection dbconn = sqlConn.GetConnection(); 
                    //DbTransaction dbtrans = dbconn.BeginTransaction();                    

                    //calling the recursive method of move
                    moveChildren(NodeId, RefNodeId, ds.Tables[1]);

                    //commit the transaction
                    //dbtrans.Commit();
                    //close the connection and transaction
                    // sqlConn.CloseDbConnection(dbconn, dbtrans);

                    //return newNodeId.ToString();
                    return movedNodeOldNewId.ToString();
                }
                else
                {
                    return "false";
                }

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "MoveNode", "MoveNode", "", ex);
                throw ex;
            }
        }

       // this is the recursive function
        public void moveChildren(Int32 NODE_ID, Int32 ParentNodeId, DataTable dtRel)
        {            
            try
            {

                Util.WritInfoLog(ClsName, "moveChildren", "Start Creating Connection");
                Connection sqlConn = new Connection();
                Util.WritInfoLog(ClsName, "moveChildren", "End Creating Connection");

                //get child nodes
                string strQry = " SELECT * FROM " + TableName;
                strQry = strQry + " WHERE PARENT_ID = " + NODE_ID;

                // strQry = strQry + " AND END_DATE  > '" + txtDate.ToString() + "'";
                strQry = strQry + " AND CONVERT(DATE,START_DATE,101) <= '" + txtDate + "'";
                strQry = strQry + " AND CONVERT(DATE,END_DATE,101) >= '" + txtDate + "'";

                //get max + 1  node_id -1
                strQry = strQry + " SELECT MAX(NODE_ID)+ 1 AS NODE_ID FROM " + TableName;

                Util.WritInfoLog(ClsName, "moveChildren", "Query for getting child node and max node id", string.Format("Query:{0}", strQry));
                DataSet ds1 = sqlConn.GetDataSetFromFactory(strQry);
                Util.WritInfoLog(ClsName, "moveChildren", "End Query for getting child node and max node id");

                DataTable Dt1 = ds1.Tables[0];

                //get node details of id which we have to move - 0
                string strQrySel = " SELECT * FROM " + TableName;
                strQrySel = strQrySel + " WHERE NODE_ID = " + NODE_ID.ToString();

                //get unique_node_id of parent_id where we have to move the node - 1
                strQrySel = strQrySel + " SELECT UNQ_NODE_ID FROM " + TableName;
                strQrySel = strQrySel + " WHERE NODE_ID = " + ParentNodeId.ToString();

                //get max + 1  node_id -2
                strQrySel = strQrySel + " SELECT MAX(NODE_ID)+ 1 AS NODE_ID FROM " + TableName;

                Util.WritInfoLog(ClsName, "moveChildren", "Query for getting details of node id which we have to move", string.Format("Query:{0}", strQry));
                DataSet ds = sqlConn.GetDataSetFromFactory(strQrySel);
                Util.WritInfoLog(ClsName, "moveChildren", "End Query for getting details of node id which we have to move");

                DataTable Dt = ds.Tables[0];

                //update end_date to node which we have to move
                string strQryUpd = " UPDATE " + TableName + " SET END_DATE = '" + DateTime.Now.AddDays(-1).ToString("MM/dd/yyyy") + "'";
                strQryUpd = strQryUpd + " WHERE NODE_ID = " + NODE_ID.ToString();

                ///change by poon on 26082011 for transaction
                Util.WritInfoLog(ClsName, "moveChildren", "Query for updating end date of node which we have to move", string.Format("Query:{0}", strQryUpd));
                Int32 ret = sqlConn.SaveDataByFactory(strQryUpd);
                Util.WritInfoLog(ClsName, "moveChildren", "End Query for updating end date of node which we have to move");
                //Int32 ret = sqlConn.SaveDataByFactory(strQryUpd, dbconn, dbtrans);              
                
                movedNodeOldNewId = movedNodeOldNewId + NODE_ID.ToString() + "," + ds.Tables[2].Rows[0]["NODE_ID"].ToString() + ";";

                //insert a new record with all data with that node just change parent_id and unq_parent_id
                string strInsert = "INSERT INTO " + TableName;
                strInsert = strInsert + "( NODE_ID,HIERARCHY_ID,VERSION_ID,NODE_TYPE_ID,UNQ_NODE_ID,NODE_NAME";
                strInsert = strInsert + ",START_DATE,END_DATE,PARENT_ID,UNQ_PARENT_NODE_ID)";
                strInsert = strInsert + " VALUES ( " + ds.Tables[2].Rows[0]["NODE_ID"].ToString() + ",";

                if (Dt.Rows.Count > 0)
                {

                    strInsert = strInsert + Dt.Rows[0]["HIERARCHY_ID"].ToString() + ",";
                    strInsert = strInsert + Dt.Rows[0]["VERSION_ID"].ToString() + ",";
                    strInsert = strInsert + Dt.Rows[0]["NODE_TYPE_ID"].ToString() + ",";
                    strInsert = strInsert + Dt.Rows[0]["UNQ_NODE_ID"].ToString() + ",";
                    strInsert = strInsert + "'" + Dt.Rows[0]["NODE_NAME"].ToString() + "'" + ",";
                    strInsert = strInsert + "'" + DateTime.Now.ToString("MM/dd/yyyy") + "'" + ",";
                    strInsert = strInsert + "'" + Convert.ToDateTime(EndDate).ToString("MM/dd/yyyy") + "'" + ",";
                    strInsert = strInsert + ParentNodeId + ",";
                    strInsert = strInsert + ds.Tables[1].Rows[0]["UNQ_NODE_ID"].ToString();
                }

                strInsert = strInsert + ")";

                ///change by poon on 26082011 for transaction
                //save node 
                Util.WritInfoLog(ClsName, "moveChildren", "Query for inserting new node", string.Format("Query:{0}", strInsert));
                int ret1 = sqlConn.SaveDataByFactory(strInsert);
                Util.WritInfoLog(ClsName, "moveChildren", "End Query for inserting new node");

                //int ret1 = sqlConn.SaveDataByFactory(strInsert, dbconn, dbtrans);

                //save attribute values
                int retattr = saveAttributes(Convert.ToInt32(ds.Tables[0].Rows[0]["NODE_ID"]), EndDate, Convert.ToInt32(ds.Tables[0].Rows[0]["NODE_TYPE_ID"]), Convert.ToInt32(ds.Tables[2].Rows[0]["NODE_ID"]));

                //mode relation data
                //Boolean bmoveRelflag = false;
                foreach (DataRow dr in dtRel.Rows)
                {
                    if (bmoveRelflag == true)
                    {
                        //update node_id with new node_id which we have moved
                        strQryUpd = " UPDATE " + Convert.ToString(dr["TABLE_NAME"]) + " SET NODE_ID = " + ds.Tables[2].Rows[0]["NODE_ID"].ToString();
                        strQryUpd = strQryUpd + " WHERE NODE_ID = " + NODE_ID.ToString();
                    }
                    else
                    {
                        //relate to root
                        strQryUpd = " UPDATE " + Convert.ToString(dr["TABLE_NAME"]) + " SET NODE_ID =1 ";
                        strQryUpd = strQryUpd + " WHERE NODE_ID = " + NODE_ID.ToString();
                    }

                    Util.WritInfoLog(ClsName, "moveChildren", "Query for moving relation data of moved node", string.Format("Query:{0}", strQryUpd));
                    int retmove = sqlConn.SaveDataByFactory(strQryUpd);
                    Util.WritInfoLog(ClsName, "moveChildren", "End Query for moving relation data of moved node");

                }

                foreach (DataRow dr in Dt1.Rows)
                {
                    Int32 parentId = Convert.ToInt32(ds1.Tables[1].Rows[0]["NODE_ID"]);
                    moveChildren(Convert.ToInt32(dr["NODE_ID"]), parentId, dtRel);
                }

            }
            catch (Exception ex)
            {
                //dbconn.Close();
                //dbconn.Dispose();
                //dbtrans.Dispose();
                Util.WritErrorLog(ClsName, "moveChildren", "moveChildren", "", ex);
                throw ex;
            }
        }
       
        //before SP
        //public void moveChildren(Int32 NODE_ID, Int32 RefNodeId, DataTable dtRel)
        //{

        //    try
        //    {

        //        Util.WritInfoLog(ClsName, "moveChildren", "Start Creating Connection");
        //        Connection sqlConn = new Connection();
        //        Util.WritInfoLog(ClsName, "moveChildren", "End Creating Connection");

        //        //get child nodes
        //        string strQry = " SELECT * FROM " + TableName;
        //        strQry = strQry + " WHERE PARENT_ID = " + NODE_ID;

        //        // strQry = strQry + " AND END_DATE  > '" + txtDate.ToString() + "'";
        //        strQry = strQry + " AND  CONVERT(DATE,START_DATE,101) <= '" + txtDate + "'";
        //        strQry = strQry + " AND CONVERT(DATE,END_DATE,101) >= '" + txtDate + "'";

        //        //get max + 1  node_id -1
        //        strQry = strQry + " SELECT MAX(NODE_ID)+ 1 AS NODE_ID FROM " + TableName;

        //        Util.WritInfoLog(ClsName, "moveChildren", "Query for getting child node and max node id", string.Format("Query:{0}", strQry));
        //        DataSet ds1 = sqlConn.GetDataSetFromFactory(strQry);
        //        Util.WritInfoLog(ClsName, "moveChildren", "End Query for getting child node and max node id");

        //        DataTable Dt1 = ds1.Tables[0];

        //        //get node details of id which we have to move - 0
        //        string strQrySel = " SELECT * FROM " + TableName;
        //        strQrySel = strQrySel + " WHERE NODE_ID = " + NODE_ID.ToString();

        //        //get unique_node_id of parent_id where we have to move the node - 1
        //        strQrySel = strQrySel + " SELECT UNQ_NODE_ID FROM " + TableName;
        //        strQrySel = strQrySel + " WHERE NODE_ID = " + RefNodeId.ToString();

        //        //get max + 1  node_id -2
        //        strQrySel = strQrySel + " SELECT MAX(NODE_ID)+ 1 AS NODE_ID FROM " + TableName;

        //        Util.WritInfoLog(ClsName, "moveChildren", "Query for getting details of node id which we have to move", string.Format("Query:{0}", strQry));
        //        DataSet ds = sqlConn.GetDataSetFromFactory(strQrySel);
        //        Util.WritInfoLog(ClsName, "moveChildren", "End Query for getting details of node id which we have to move");

        //        DataTable Dt = ds.Tables[0];

        //        //update end_date to node which we have to move
        //        string strQryUpd = " UPDATE " + TableName + " SET END_DATE = '" + DateTime.Now.AddDays(-1).ToString("MM/dd/yyyy") + "'";
        //        strQryUpd = strQryUpd + " WHERE NODE_ID = " + NODE_ID.ToString();

        //        ///change by poon on 26082011 for transaction
        //        Util.WritInfoLog(ClsName, "moveChildren", "Query for updating end date of node which we have to move", string.Format("Query:{0}", strQryUpd));
        //        Int32 ret = sqlConn.SaveDataByFactory(strQryUpd);
        //        Util.WritInfoLog(ClsName, "moveChildren", "End Query for updating end date of node which we have to move");
        //        //Int32 ret = sqlConn.SaveDataByFactory(strQryUpd, dbconn, dbtrans);              


        //        //movedNodeOldNewId = movedNodeOldNewId + NODE_ID.ToString() + "," + ds.Tables[2].Rows[0]["NODE_ID"].ToString() + ";";

        //        //insert a new record with all data with that node just change parent_id and unq_parent_id
        //        string strInsert = "INSERT INTO " + TableName;
        //        strInsert = strInsert + "( NODE_ID,HIERARCHY_ID,VERSION_ID,NODE_TYPE_ID,UNQ_NODE_ID,NODE_NAME";
        //        strInsert = strInsert + ",START_DATE,END_DATE,PARENT_ID,UNQ_PARENT_NODE_ID)";
        //        strInsert = strInsert + " VALUES ( " + ds.Tables[2].Rows[0]["NODE_ID"].ToString() + ",";

        //        if (Dt.Rows.Count > 0)
        //        {

        //            strInsert = strInsert + Dt.Rows[0]["HIERARCHY_ID"].ToString() + ",";
        //            strInsert = strInsert + Dt.Rows[0]["VERSION_ID"].ToString() + ",";
        //            strInsert = strInsert + Dt.Rows[0]["NODE_TYPE_ID"].ToString() + ",";
        //            strInsert = strInsert + Dt.Rows[0]["UNQ_NODE_ID"].ToString() + ",";
        //            strInsert = strInsert + "'" + Dt.Rows[0]["NODE_NAME"].ToString() + "'" + ",";
        //            strInsert = strInsert + "'" + DateTime.Now.ToString("MM/dd/yyyy") + "'" + ",";
        //            strInsert = strInsert + "'" + Convert.ToDateTime(EndDate).ToString("MM/dd/yyyy") + "'" + ",";
        //            strInsert = strInsert + RefNodeId + ",";
        //            strInsert = strInsert + ds.Tables[1].Rows[0]["UNQ_NODE_ID"].ToString();
        //        }

        //        strInsert = strInsert + ")";

        //        ///change by poon on 26082011 for transaction
        //        //save node 
        //        Util.WritInfoLog(ClsName, "moveChildren", "Query for inserting new node", string.Format("Query:{0}", strInsert));
        //        int ret1 = sqlConn.SaveDataByFactory(strInsert);
        //        Util.WritInfoLog(ClsName, "moveChildren", "End Query for inserting new node");

        //        //int ret1 = sqlConn.SaveDataByFactory(strInsert, dbconn, dbtrans);

        //        //save attribute values
        //        int retattr = saveAttributes(Convert.ToInt32(ds.Tables[0].Rows[0]["NODE_ID"]), EndDate, Convert.ToInt32(ds.Tables[0].Rows[0]["NODE_TYPE_ID"]), Convert.ToInt32(ds.Tables[2].Rows[0]["NODE_ID"]));

        //        //mode relation data
        //        //Boolean bmoveRelflag = false;
        //        foreach (DataRow dr in dtRel.Rows)
        //        {
        //            if (bmoveRelflag == true)
        //            {
        //                //update node_id with new node_id which we have moved
        //                strQryUpd = " UPDATE " + Convert.ToString(dr["TABLE_NAME"]) + " SET NODE_ID = " + ds.Tables[2].Rows[0]["NODE_ID"].ToString();
        //                strQryUpd = strQryUpd + " WHERE NODE_ID = " + NODE_ID.ToString();
        //            }
        //            else
        //            {
        //                //relate to root
        //                strQryUpd = " UPDATE " + Convert.ToString(dr["TABLE_NAME"]) + " SET NODE_ID =1 ";
        //                strQryUpd = strQryUpd + " WHERE NODE_ID = " + NODE_ID.ToString();
        //            }

        //            Util.WritInfoLog(ClsName, "moveChildren", "Query for moving relation data of moved node", string.Format("Query:{0}", strQryUpd));
        //            int retmove = sqlConn.SaveDataByFactory(strQryUpd);
        //            Util.WritInfoLog(ClsName, "moveChildren", "End Query for moving relation data of moved node");

        //        }

        //        foreach (DataRow dr in Dt1.Rows)
        //        {
        //            Int32 refid = Convert.ToInt32(ds1.Tables[1].Rows[0]["NODE_ID"]);
        //            moveChildren(Convert.ToInt32(dr["NODE_ID"]), refid, dtRel);
        //        }

        //    }
        //    catch (Exception ex)
        //    {
        //        //dbconn.Close();
        //        //dbconn.Dispose();
        //        //dbtrans.Dispose();
        //        Util.WritErrorLog(ClsName, "moveChildren", "moveChildren", "", ex);
        //        throw ex;
        //    }
        //}

        public bool ValidateMove()
        {

            try
            {


                Util.WritInfoLog(ClsName, "ValidateMove", "Start Creating Connection");
                Connection sqlConn = new Connection();
                Util.WritInfoLog(ClsName, "ValidateMove", "End Creating Connection");

                string strQry = "SELECT COUNT(*) FROM X_HIER_NODE_TYPE_PARENT WHERE NODE_TYPE_ID=";
                strQry = strQry + "(SELECT NODE_TYPE_ID FROM " + TableName + " WHERE NODE_ID= " + NodeId + ")";
                strQry = strQry + " AND PARENT_NODE_TYPE_ID =";
                strQry = strQry + "(SELECT NODE_TYPE_ID FROM " + TableName + " WHERE NODE_ID= " + RefNodeId + ")";
                strQry = strQry + " AND HIERARCHY_ID =" + HierarchyId;

                Util.WritInfoLog(ClsName, "ValidateMove", "Query for ValidateMove", string.Format("Query:{0}", strQry));
                Int32 intCnt = Convert.ToInt32(sqlConn.ExecuteScalarByFactory(strQry));
                Util.WritInfoLog(ClsName, "ValidateMove", "End Query for ValidateMove");

                if (intCnt == 0)
                {
                    return false;
                }
                else
                {
                    return true;
                }
            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "ValidateMove", "ValidateMove", "", ex);
                throw ex;
            }

        }

        //before SP
        //public bool ValidateMove()
        //{

        //    try
        //    {


        //        Util.WritInfoLog(ClsName, "ValidateMove", "Start Creating Connection");
        //        Connection sqlConn = new Connection();
        //        Util.WritInfoLog(ClsName, "ValidateMove", "End Creating Connection");

        //        string strQry = "SELECT COUNT(*) FROM X_HIER_NODE_TYPE_PARENT WHERE NODE_TYPE_ID=";
        //        strQry = strQry + "(SELECT NODE_TYPE_ID FROM " + TableName + " WHERE NODE_ID= " + NodeId + ")";
        //        strQry = strQry + " AND PARENT_NODE_TYPE_ID =";
        //        strQry = strQry + "(SELECT NODE_TYPE_ID FROM " + TableName + " WHERE NODE_ID= " + RefNodeId + ")";
        //        strQry = strQry + " AND HIERARCHY_ID =" + HierarchyId;

        //        Util.WritInfoLog(ClsName, "ValidateMove", "Query for ValidateMove", string.Format("Query:{0}", strQry));
        //        Int32 intCnt = Convert.ToInt32(sqlConn.ExecuteScalarByFactory(strQry));
        //        Util.WritInfoLog(ClsName, "ValidateMove", "End Query for ValidateMove");

        //        if (intCnt == 0)
        //        {
        //            return false;
        //        }
        //        else
        //        {
        //            return true;
        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        Util.WritErrorLog(ClsName, "ValidateMove", "ValidateMove", "", ex);
        //        throw ex;
        //    }
        //}

        public string RemoveNode()
        {

            try
            {

                bool bValidate = ValidateRemove();

                if (bValidate == true)
                {

                    Util.WritInfoLog(ClsName, "RemoveNode", "Start Creating Connection");
                    Connection sqlConn = new Connection();
                    Util.WritInfoLog(ClsName, "RemoveNode", "End Creating Connection");

                    //update end_date to node which we have to remove
                    List<DBParameter> lstparam = new List<DBParameter>();
                    lstparam.Add(new DBParameter("@TableName", DbType.String, ParameterDirection.Input, 255, TableName));
                    lstparam.Add(new DBParameter("@END_DATE", DbType.String, ParameterDirection.Input, 10, DateTime.Now.AddDays(-1).ToString("MM/dd/yyyy")));
                    lstparam.Add(new DBParameter("@NODE_ID", DbType.Int32, ParameterDirection.Input, 0, NodeId));

                    Util.WritInfoLog(ClsName, "RemoveNode", "method of usp_RemoveNode", string.Format("method:{0}", "usp_RemoveNode"));
                    Int32 ret = Convert.ToInt32(sqlConn.SaveDataByFactoryByProc("usp_RemoveNode", ref(lstparam)));
                    Util.WritInfoLog(ClsName, "RemoveNode", "End method of usp_RemoveNode");

                    return "true";

                }
                else
                {
                    return "false";
                }

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "RemoveNode", "RemoveNode", "", ex);
                return "false";
                throw ex;
            }
        }

        //before SP
        //public string RemoveNode()
        //{

        //    try
        //    {

        //        bool bValidate = ValidateRemove();
        //        if (bValidate == true)
        //        {

        //            Util.WritInfoLog(ClsName, "RemoveNode", "Start Creating Connection");
        //            Connection sqlConn = new Connection();
        //            Util.WritInfoLog(ClsName, "RemoveNode", "End Creating Connection");

        //            //update end_date to node which we have to remove
        //            string strQryUpd = " UPDATE " + TableName + " SET END_DATE = '" + DateTime.Now.AddDays(-1).ToString("MM/dd/yyyy") + "'";
        //            strQryUpd = strQryUpd + " WHERE NODE_ID = " + NodeId.ToString();

        //            Util.WritInfoLog(ClsName, "RemoveNode", "Query for RemoveNode", string.Format("Query:{0}", strQryUpd));
        //            Int32 ret = sqlConn.SaveDataByFactory(strQryUpd);
        //            Util.WritInfoLog(ClsName, "RemoveNode", "End Query for RemoveNode");

        //            return "true";

        //        }
        //        else
        //        {
        //            return "false";
        //        }

        //    }
        //    catch (Exception ex)
        //    {
        //        Util.WritErrorLog(ClsName, "RemoveNode", "RemoveNode", "", ex);
        //        throw ex;
        //    }
        //}

        public bool ValidateRemove()
        {

            try
            {

                Util.WritInfoLog(ClsName, "ValidateRemove", "Start Creating Connection");
                Connection sqlConn = new Connection();
                Util.WritInfoLog(ClsName, "ValidateRemove", "End Creating Connection");

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@TableName", DbType.String, ParameterDirection.Input, 255, TableName));
                lstparam.Add(new DBParameter("@txtDate", DbType.DateTime, ParameterDirection.Input, 10, txtDate));
                lstparam.Add(new DBParameter("@NODE_ID", DbType.Int32, ParameterDirection.Input, 0, NodeId));

                Util.WritInfoLog(ClsName, "ValidateRemove", "method of usp_ValidateRemove", string.Format("method:{0}", "usp_ValidateRemove"));
                Int32 intCnt = Convert.ToInt32(sqlConn.SaveDataByFactoryByProc("usp_ValidateRemove", ref(lstparam)));
                Util.WritInfoLog(ClsName, "ValidateRemove", "End method of usp_ValidateRemove");

                if (intCnt == 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "ValidateRemove", "ValidateRemove", "", ex);
                throw ex;
            }
        }

        //before SP
        //public bool ValidateRemove()
        //{

        //    try
        //    {

        //        Util.WritInfoLog(ClsName, "ValidateRemove", "Start Creating Connection");
        //        Connection sqlConn = new Connection();
        //        Util.WritInfoLog(ClsName, "ValidateRemove", "End Creating Connection");

        //        string strQry = "SELECT COUNT(*) FROM " + TableName + " WHERE PARENT_ID=" + NodeId;
        //        strQry = strQry + " AND CONVERT(DATE,START_DATE,101) <= '" + txtDate + "'";
        //        strQry = strQry + " AND CONVERT(DATE,END_DATE,101)>= '" + txtDate + "'";

        //        Util.WritInfoLog(ClsName, "ValidateRemove", "Query for ValidateRemove", string.Format("Query:{0}", strQry));
        //        Int32 intCnt = Convert.ToInt32(sqlConn.ExecuteScalarByFactory(strQry));
        //        Util.WritInfoLog(ClsName, "ValidateRemove", "End Query for ValidateRemove");

        //        if (intCnt == 0)
        //        {
        //            return true;
        //        }
        //        else
        //        {
        //            return false;
        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        Util.WritErrorLog(ClsName, "ValidateRemove", "ValidateRemove", "", ex);
        //        throw ex;
        //    }

        //}

        public string CheckNode()
        {

            try
            {

                Util.WritInfoLog(ClsName, "CheckNode", "Start Creating Connection");
                Connection sqlConn = new Connection();
                Util.WritInfoLog(ClsName, "CheckNode", "End Creating Connection");

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@HierarchyId", DbType.Int32, ParameterDirection.Input, 0, HierarchyId));
                lstparam.Add(new DBParameter("@VersionId", DbType.Int32, ParameterDirection.Input, 0, VersionId));
                lstparam.Add(new DBParameter("@txtDate", DbType.DateTime, ParameterDirection.Input, 10, txtDate));

                Util.WritInfoLog(ClsName, "CheckNode", "method of usp_CheckNode", string.Format("method:{0}", "usp_CheckNode"));
                Int32 intCnt = Convert.ToInt32(sqlConn.ExecuteScalarByFactory("usp_CheckNode", ref(lstparam)));
                Util.WritInfoLog(ClsName, "CheckNode", "End method of usp_CheckNode");

                if (intCnt == 0)
                {
                    return "false";
                }
                else
                {
                    return "true";
                }
            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "CheckNode", "CheckNode", "", ex);
                return "false";
                throw ex;
            }
        }

        //before SP
        //public string CheckNode()
        //{

        //    try
        //    {

        //        Util.WritInfoLog(ClsName, "CheckNode", "Start Creating Connection");
        //        Connection sqlConn = new Connection();
        //        Util.WritInfoLog(ClsName, "CheckNode", "End Creating Connection");

        //        //get max + 1  node_id -2
        //        string strQry = " SELECT COUNT(*) FROM X_HIER_VERSIONS WHERE HIERARCHY_ID=" + HierarchyId;
        //        strQry = strQry + " AND VERSION_ID =" + VersionId;
        //        strQry = strQry + " AND '" + txtDate + "' <= CONVERT(DATE,START_DATE,101) ";

        //        Util.WritInfoLog(ClsName, "CheckNode", "Query for CheckNode", string.Format("Query:{0}", strQry));
        //        Int32 intCnt = Convert.ToInt32(sqlConn.ExecuteScalarByFactory(strQry));
        //        Util.WritInfoLog(ClsName, "CheckNode", "End Query for CheckNode");

        //        if (intCnt == 0)
        //        {
        //            return "false";
        //        }
        //        else
        //        {
        //            return "true";
        //        }

        //    }
        //    catch (Exception ex)
        //    {
        //        Util.WritErrorLog(ClsName, "CheckNode", "CheckNode", "", ex);
        //        throw ex;
        //    }
        //}

        public string GetDim()
        {

            try
            {


                Util.WritInfoLog(ClsName, "GetDim", "Start Creating Connection");
                Connection sqlConn = new Connection();
                Util.WritInfoLog(ClsName, "GetDim", "End Creating Connection");

                //get dimension for selected hierarchy
                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@HierarchyId", DbType.Int32, ParameterDirection.Input, 0, HierarchyId));

                Util.WritInfoLog(ClsName, "GetDim", "method of usp_GetDim", string.Format("method:{0}", "usp_GetDim"));
                DataSet ds = sqlConn.GetDataSetFromFactoryProc("usp_GetDim", ref(lstparam));
                Util.WritInfoLog(ClsName, "GetDim", "End method of usp_GetDim");

                List<Dimensions> listDim = new List<Dimensions>();

                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    Dimensions dim = new Dimensions();
                    dim.dimname = dr["LIST_NAME"].ToString();
                    dim.dimabbr = dr["TABLE_ABBR"].ToString();
                    listDim.Add(dim);
                }

                JavaScriptSerializer js = new JavaScriptSerializer();
                return js.Serialize(listDim);

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "GetDim", "GetDim", "", ex);
                throw ex;
            }
        }

        //beforeSP
        //public string GetDim()
        //{

        //    try
        //    {

        //        Util.WritInfoLog(ClsName, "GetDim", "Start Creating Connection");
        //        Connection sqlConn = new Connection();
        //        Util.WritInfoLog(ClsName, "GetDim", "End Creating Connection");

        //        //get dimension for selected hierarchy
        //        string strQry = " SELECT * FROM X_HIER_MASTER_LIST_REL ";
        //        strQry = strQry + " INNER JOIN X_MASTER_LIST ";
        //        strQry = strQry + " ON X_HIER_MASTER_LIST_REL.MASTER_LIST_ID = X_MASTER_LIST.LIST_ID";
        //        strQry = strQry + " WHERE HIERARCHY_ID=" + HierarchyId;

        //        Util.WritInfoLog(ClsName, "GetDim", "Query for GetDim", string.Format("Query:{0}", strQry));
        //        DataSet ds = sqlConn.GetDataSetFromFactory(strQry);
        //        Util.WritInfoLog(ClsName, "GetDim", "End Query for GetDim");

        //        List<Dimensions> listDim = new List<Dimensions>();

        //        foreach (DataRow dr in ds.Tables[0].Rows)
        //        {

        //            //Dictionary<string, string> dirdimen = new Dictionary<string, string>();
        //            Dimensions dim = new Dimensions();
        //            dim.dimname = dr["LIST_NAME"].ToString();
        //            dim.dimabbr = dr["TABLE_ABBR"].ToString();
        //            listDim.Add(dim);
        //        }

        //        JavaScriptSerializer js = new JavaScriptSerializer();
        //        // return js.Serialize(lstDimens);
        //        return js.Serialize(listDim);

        //    }
        //    catch (Exception ex)
        //    {
        //        Util.WritErrorLog(ClsName, "GetDim", "GetDim", "", ex);
        //        throw ex;
        //    }
        //}

        /// <summary>
        /// get dimension grid data for both exist and un-assigned
        /// </summary>
        /// <param name="bOnlydata"></param>
        /// <returns></returns>
        public string GetDimGriddata(Boolean bOnlydata)
        {
            try
            {

                Dimension dim = new Dimension();
                if (NodeId != 0)
                {

                    Grid gridexist = GetDimGriddataExist(bOnlydata);
                    dim.gridEx = gridexist;

                    //un-assign data - not needed now
                    //Grid gridun = GetDimGriddataUn(bOnlydata);
                    //dim.gridUnassign = gridun;

                    //get rules
                    rules rules = GetRules();
                    dim.rules = rules;
                }

                JavaScriptSerializer js = new JavaScriptSerializer();
                return js.Serialize(dim);

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "GetDimGriddata", "GetDimGriddata", "", ex);
                throw ex;
            }
        }

        //Get Dimension grid data for un-assigned
        public string GetDimGriddataUnasgn(Boolean bOnlydata)
        {
            try
            {

                Dimension dim = new Dimension();
                // if (NodeId != 0)
                //  {
                //Grid gridexist = GetDimGriddataExist();
                //dim.gridEx = gridexist;

                //Grid gridun = GetDimGriddataUn(bOnlydata);
                //dim.gridUnassign = gridun;

                Grid gridexist = GetDimGriddataExist(bOnlydata);
                dim.gridEx = gridexist;

                //}

                JavaScriptSerializer js = new JavaScriptSerializer();
                return js.Serialize(dim);

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "GetDimGriddataUnasgn", "GetDimGriddataUnasgn", "", ex);
                throw ex;
            }
        }

        public rules GetRules()
        {
            try
            {

                rules rules = new rules();

                Util.WritInfoLog(ClsName, "GetRules", "Start Creating Connection");
                Connection sqlConn = new Connection();
                Util.WritInfoLog(ClsName, "GetRules", "End Creating Connection");


                string strQry = " SELECT * FROM X_MASTER_LIST WHERE TABLE_ABBR= '" + dimname + "'";

                strQry = strQry + " SELECT X_HIER_MASTER_LIST_RULES.TABLE_NAME FROM X_HIER_MASTER_LIST_RULES ";
                strQry = strQry + " INNER JOIN X_MASTER_LIST ON ";
                strQry = strQry + " X_HIER_MASTER_LIST_RULES.MASTER_LIST_ID =X_MASTER_LIST.LIST_ID ";
                strQry = strQry + " WHERE HIERARCHY_ID= " + HierarchyId;
                strQry = strQry + " AND TABLE_ABBR= '" + dimname + "'";

                Util.WritInfoLog(ClsName, "GetRules", "Query for getting details befor Getting Rules", string.Format("Query:{0}", strQry));
                DataSet ds = sqlConn.GetDataSetFromFactory(strQry);
                Util.WritInfoLog(ClsName, "GetRules", "End Query for getting details befor Getting Rules");

                DataTable dtRules = ds.Tables[1];

                //string strRule = "  WITH Hierarchy(Node_id,Node_name, Parent_ID, HLevel,CRITERIA,CRITERIA_OBJECT) AS ( ";
                //strRule = strRule + " SELECT " + TableName + ".Node_id,Node_name,Parent_ID,   ";
                //strRule = strRule + " 0 as HLevel ,CRITERIA,CRITERIA_OBJECT FROM " + TableName;
                //strRule = strRule + " INNER JOIN " + dtRules.Rows[0]["TABLE_NAME"];
                //strRule = strRule + "  ON " + TableName + ".NODE_ID= " + dtRules.Rows[0]["TABLE_NAME"] + ".NODE_ID";
                //strRule = strRule + " WHERE " + TableName + ".Node_id =  " + NodeId;
                //strRule = strRule + " UNION ALL ";
                //strRule = strRule + " SELECT  parent.Node_id,   parent.[Node_name], ";
                //strRule = strRule + " parent.Parent_ID ,    ";
                //strRule = strRule + " HLevel + 1 ," + dtRules.Rows[0]["TABLE_NAME"] + ".criteria," + dtRules.Rows[0]["TABLE_NAME"] + ".CRITERIA_OBJECT FROM " + TableName;
                //strRule = strRule + " parent INNER JOIN ";
                //strRule = strRule + dtRules.Rows[0]["TABLE_NAME"];
                //strRule = strRule + " ON parent.NODE_ID=" + dtRules.Rows[0]["TABLE_NAME"] + ".NODE_ID  ";
                //strRule = strRule + " INNER JOIN     Hierarchy  ";
                //strRule = strRule + " ON  parent.Node_id = Hierarchy.Parent_ID  )";
                //strRule = strRule + " SELECT  Node_id,     Node_name,      Parent_ID, ";
                //strRule = strRule + " HLevel,criteria,CRITERIA_OBJECT  FROM  Hierarchy ORDER BY     HLevel DESC";


                string strRule = "  WITH Hierarchy(Node_id,Node_name, Parent_ID, HLevel) AS ( ";
                strRule = strRule + " SELECT " + TableName + ".Node_id,Node_name,Parent_ID,   ";
                strRule = strRule + " 0 as HLevel FROM " + TableName;
                //strRule = strRule + " INNER JOIN " + dtRules.Rows[0]["TABLE_NAME"];
                //strRule = strRule + "  ON " + TableName + ".NODE_ID= " + dtRules.Rows[0]["TABLE_NAME"] + ".NODE_ID";
                strRule = strRule + " WHERE " + TableName + ".Node_id =  " + NodeId;
                strRule = strRule + " UNION ALL ";
                strRule = strRule + " SELECT  parent.Node_id,   parent.[Node_name], ";
                strRule = strRule + " parent.Parent_ID ,    ";
                strRule = strRule + " HLevel + 1 ";
                strRule = strRule + " FROM " + TableName + " parent ";
                //strRule = strRule + "INNER JOIN ";
                //strRule = strRule + dtRules.Rows[0]["TABLE_NAME"];
                //strRule = strRule + " ON parent.NODE_ID=" + dtRules.Rows[0]["TABLE_NAME"] + ".NODE_ID  ";
                strRule = strRule + " INNER JOIN  Hierarchy  ";
                strRule = strRule + " ON  parent.Node_id = Hierarchy.Parent_ID  )";

                strRule = strRule + " SELECT  Her.Node_id,Node_name,Parent_ID, ";
                strRule = strRule + " HLevel,criteria,CRITERIA_OBJECT  FROM  Hierarchy HER ";
                strRule = strRule + "INNER JOIN ";
                strRule = strRule + dtRules.Rows[0]["TABLE_NAME"] + " RULES ";
                strRule = strRule + " ON HER.NODE_ID=RULES.NODE_ID  ";

                strRule = strRule + " AND CONVERT(DATE,START_DATE,101) <= '" + txtDate.ToString("MM/dd/yyyy") + "'";
                strRule = strRule + " AND CONVERT(DATE,END_DATE,101) >= '" + txtDate.ToString("MM/dd/yyyy") + "'";

                strRule = strRule + " ORDER BY START_DATE desc,HLevel DESC ";

                Util.WritInfoLog(ClsName, "GetRules", "Query for GetRules", string.Format("Query:{0}", strRule));
                DataSet dsRules = sqlConn.GetDataSetFromFactory(strRule);
                Util.WritInfoLog(ClsName, "GetRules", "End Query for GetRules");

                DataTable dt = dsRules.Tables[0];
                DataView dv = new DataView(dt);
                dv.RowFilter = " NODE_ID =" + NodeId;
                string strAncestorsRule = string.Empty;

                if (dv.Count > 0)
                {
                    rules.currentRule = Convert.ToString(dv[0]["CRITERIA"]);
                    rules.currentObj = Convert.ToString(dv[0]["CRITERIA_OBJECT"]);
                }

                dv.RowFilter = " NODE_ID <>" + NodeId + " AND CRITERIA IS NOT NULL";

                for (int i = 0; i < dv.Count; i++)
                {
                    if (Convert.ToString(dv[i]["CRITERIA"]) != "")
                    {
                        if (strAncestorsRule == "")
                        {
                            strAncestorsRule = strAncestorsRule + Convert.ToString(dv[i]["CRITERIA"]);
                        }
                        else
                        {
                            strAncestorsRule = strAncestorsRule + " AND " + Convert.ToString(dv[i]["CRITERIA"]);
                        }
                    }
                }

                rules.ancesrtosRule = strAncestorsRule;
                return rules;

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "GetRules", "GetRules", "", ex);
                throw ex;
            }
        }


        ////get existing data of a node
        //public Grid GetDimGriddataExist_(Boolean bOnlydata)
        //{
        //    try
        //    {

        //        string primarykey = string.Empty;
        //        Grid griddata = new Grid();

        //        Connection sqlConn = new Connection();
        //        string strQry = " SELECT * FROM X_MASTER_LIST WHERE TABLE_ABBR= '" + dimname + "'";

        //        strQry = strQry + " SELECT * FROM X_MASTER_LIST_COLUMNS ";
        //        strQry = strQry + " WHERE LIST_ID= (SELECT LIST_ID FROM X_MASTER_LIST ";
        //        strQry = strQry + " WHERE TABLE_ABBR=  '" + dimname + "')";
        //        //strQry = strQry + " AND  IS_PRIMARY=  'false'";

        //        strQry = strQry + " SELECT X_HIER_MASTER_LIST_REL.TABLE_NAME FROM X_HIER_MASTER_LIST_REL ";
        //        strQry = strQry + " INNER JOIN X_MASTER_LIST ON ";
        //        strQry = strQry + " X_HIER_MASTER_LIST_REL.MASTER_LIST_ID =X_MASTER_LIST.LIST_ID ";
        //        strQry = strQry + " WHERE HIERARCHY_ID= " + HierarchyId;
        //        strQry = strQry + " AND TABLE_ABBR= '" + dimname + "'";

        //        DataSet ds = sqlConn.GetDataSetFromFactory(strQry);
        //        DataTable dtCol = ds.Tables[1];
        //        DataView DvCol = new DataView(dtCol);
        //        DvCol.RowFilter = " IS_PRIMARY =TRUE";

        //        string strDim = " SELECT * FROM " + ds.Tables[0].Rows[0]["TABLE_NAME"];
        //        strDim = strDim + " INNER JOIN " + ds.Tables[2].Rows[0]["TABLE_NAME"];
        //        strDim = strDim + " ON " + ds.Tables[0].Rows[0]["TABLE_NAME"] + "." + DvCol[0]["FIELD_NAME"];
        //        strDim = strDim + " = " + ds.Tables[2].Rows[0]["TABLE_NAME"] + "." + DvCol[0]["FIELD_NAME"];
        //        strDim = strDim + " WHERE NODE_ID= " + NodeId;

        //        //strDim = strDim + " AND CONVERT(VARCHAR,END_DATE,101) >= '" + txtDate.ToString("MM/dd/yyyy") + "'";
        //        //strDim = strDim + " AND CONVERT(VARCHAR,START_DATE,101) <= '" + txtDate.ToString("MM/dd/yyyy") + "'";
        //        strDim = strDim + " AND CONVERT(DATE,END_DATE,101) >= '" +txtDate + "'";
        //        strDim = strDim + " AND CONVERT(DATE,START_DATE,101) <= '" + txtDate + "'";

        //        // change this 
        //        //SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('M_PRODUCTS')
        //        strDim = strDim + " SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('" + ds.Tables[0].Rows[0]["TABLE_NAME"];
        //        strDim = strDim + " ')";

        //        //strDim = strDim + " SELECT * FROM " + ds.Tables[0].Rows[0]["TABLE_NAME"];
        //        //strDim = strDim + " WHERE 1=0";

        //        DataSet dsData = sqlConn.GetDataSetFromFactory(strDim);
        //        DataTable dt = dsData.Tables[0];
        //        DataTable dtColnm = dsData.Tables[1];

        //        List<colModel> listColmodel = new List<colModel>();
        //        List<string> columns = new List<string>();

        //        int index = 0;
        //        //add rel_id
        //        columns.Add("REL_ID");
        //        colModel colmodel1 = new colModel();
        //        colmodel1.index = "REL_ID";
        //        colmodel1.name =  "REL_ID";
        //        colmodel1.key = true;
        //        listColmodel.Add(colmodel1);

        //        DvCol.RowFilter = " IS_PRIMARY =FALSE";
        //        //index++;
        //        //
        //        //actual data 
        //        List<object> rows = new List<object>();
        //        foreach (DataRow dr in dt.Rows)
        //        {

        //            Dictionary<string, object> row = new Dictionary<string, object>();
        //            row.Add("REL_ID", dr["REL_ID"]);
        //            foreach (DataRow drcol in dtColnm.Rows)
        //            {
        //                row.Add(drcol["name"].ToString(), dr[drcol["name"].ToString()]);
        //            }

        //            rows.Add(row);
        //        }

        //        griddata.datarow = rows;


        //        if (bOnlydata == false)
        //        {
        //            //foreach (DataRow dr in ds.Tables[1].Rows)
        //            foreach (DataRowView dr in DvCol)
        //            {

        //                //for columns
        //                columns.Add(dr["DISPLAY"].ToString());
        //                //for colmodel
        //                colModel colmodel = new colModel();

        //                colmodel.index = dr["FIELD_NAME"].ToString();
        //                colmodel.name = dr["FIELD_NAME"].ToString();

        //                Dictionary<string, object> edoptions = new Dictionary<string, object>();
        //                Dictionary<string, object> editrules = new Dictionary<string, object>();
        //                Dictionary<string, object> formatoptions = new Dictionary<string, object>();

        //                if (dr["DATA_TYPE"].ToString().ToLower() == "number") //integer for whole number
        //                {
        //                    colmodel.sorttype = "int";
        //                    colmodel.align = "right";

        //                    editrules.Add("number", true);
        //                    edoptions.Add("NullIfEmpty", true);
        //                    //colmodel.NullIfEmpty = "true";                        
        //                    //formatter 
        //                    colmodel.formatter = "number";
        //                    //format option for number
        //                    formatoptions.Add("decimalSeparator", ".");
        //                    formatoptions.Add("defaulValue", 0);
        //                    formatoptions.Add("decimalPlaces", 2);
        //                }
        //                else if (dr["DATA_TYPE"].ToString().ToLower() == "varchar")
        //                {
        //                    colmodel.sorttype = "text";
        //                    colmodel.align = "left";
        //                    //formatter
        //                    // colmodel.formatter = "NullFormatter";//defaultFormat
        //                    colmodel.formatter = "defaultFormat";
        //                }
        //                else if (dr["DATA_TYPE"].ToString().ToLower() == "date")
        //                {

        //                    colmodel.sorttype = "date";
        //                    colmodel.align = "right";

        //                    //edoptions.Add("dataInit","function(el) {  $(el).datepicker({ dateFormat: 'mm-dd-yy' });");
        //                    //edoptions.Add("dataInit"," $(el).datepicker();");
        //                    editrules.Add("date", true);
        //                    colmodel.formatter = "date";
        //                    ////date format in mm/dd/yyyy
        //                    formatoptions.Add("srcformat", "ISO8601Long");
        //                    formatoptions.Add("newformat", "m/d/Y");

        //                    //edoptions.Add("type", "date");
        //                    //edoptions.Add("editoptions", "js:function(elm){}");                         
        //                    // "editoptions"=>array("editoptions"=> 
        //                    //"js:function(elm){setTimeout(function(){ 
        //                    //    jQuery(elm).datepicker({dateFormat:'yy-mm-dd'}); 
        //                    //    jQuery('.ui-datepicker').css({'font-size':'75%'}); 
        //                    //},200);}") 

        //                }
        //                else if (dr["DATA_TYPE"].ToString().ToLower() == "float")
        //                {
        //                    colmodel.sorttype = "float";
        //                    colmodel.align = "right";
        //                }

        //                listColmodel.Add(colmodel);
        //                index++;
        //            }


        //            primarykey = "REL_ID";
        //            griddata.colModelList = listColmodel;
        //            griddata.columns = columns;
        //            griddata.primarykey = primarykey;

        //        }

        //       // primarykey = DvCol[0]["FIELD_NAME"].ToString();
        //        return griddata;

        //    }
        //    catch (Exception ex)
        //    {
        //        throw ex;
        //    }
        //}


        //get existing data of a node

        public Grid GetDimGriddataExist(Boolean bOnlydata)
        {
            try
            {

                Grid griddata = GetjqgridCol(bOnlydata, "exist");
                return griddata;

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "GetDimGriddataExist", "GetDimGriddataExist", "", ex);
                throw ex;
            }
        }

        ////get unassigned grid data
        //public Grid GetDimGriddataUn_(Boolean bOnlydata)
        //{
        //    try
        //    {

        //        string primarykey = string.Empty;
        //        Grid griddata = new Grid();

        //        Connection sqlConn = new Connection();
        //        string strQry = " SELECT * FROM X_MASTER_LIST WHERE TABLE_ABBR= '" + dimname + "'";

        //        strQry = strQry + " SELECT * FROM X_MASTER_LIST_COLUMNS ";
        //        strQry = strQry + " WHERE LIST_ID= (SELECT LIST_ID FROM X_MASTER_LIST ";
        //        strQry = strQry + " WHERE TABLE_ABBR=  '" + dimname + "')";

        //        strQry = strQry + " SELECT X_HIER_MASTER_LIST_REL.TABLE_NAME FROM X_HIER_MASTER_LIST_REL ";
        //        strQry = strQry + " INNER JOIN X_MASTER_LIST ON ";
        //        strQry = strQry + " X_HIER_MASTER_LIST_REL.MASTER_LIST_ID =X_MASTER_LIST.LIST_ID ";
        //        strQry = strQry + " WHERE HIERARCHY_ID= " + HierarchyId;
        //        strQry = strQry + " AND TABLE_ABBR= '" + dimname + "'";

        //        DataSet ds = sqlConn.GetDataSetFromFactory(strQry);
        //        DataTable dtCol = ds.Tables[1];
        //        DataView DvCol = new DataView(dtCol);
        //        DvCol.RowFilter = " IS_PRIMARY =TRUE";

        //        //query for un-assigned
        //        //SELECT C_PRD_PROD_REL.PRODUCT_ID ,*  FROM  M_PRODUCTS LEFT OUTER JOIN C_PRD_PROD_REL   
        //        //ON (M_PRODUCTS.PRODUCT_ID = C_PRD_PROD_REL.PRODUCT_ID)   
        //        //WHERE C_PRD_PROD_REL.PRODUCT_ID IS NULL          

        //        //not exist 
        //        string strDim = " SELECT * FROM " + ds.Tables[0].Rows[0]["TABLE_NAME"];
        //        strDim = strDim + " WHERE NOT EXISTS ( SELECT  NULL FROM " + ds.Tables[2].Rows[0]["TABLE_NAME"];
        //        strDim = strDim + " WHERE " + ds.Tables[0].Rows[0]["TABLE_NAME"] + "." + DvCol[0]["FIELD_NAME"];
        //        strDim = strDim + " = " + ds.Tables[2].Rows[0]["TABLE_NAME"] + "." + DvCol[0]["FIELD_NAME"]  ;
        //        strDim = strDim + " AND CONVERT(DATE,END_DATE,101) >= '" + txtDate + "'";
        //        strDim = strDim + " AND CONVERT(DATE,START_DATE,101) <= '" + txtDate + "')";

        //        // left join and null
        //        //string strDim = " SELECT * FROM " + ds.Tables[0].Rows[0]["TABLE_NAME"];
        //        //strDim = strDim + " LEFT OUTER JOIN " + ds.Tables[2].Rows[0]["TABLE_NAME"];
        //        //strDim = strDim + " ON " + ds.Tables[0].Rows[0]["TABLE_NAME"] + "." + DvCol[0]["FIELD_NAME"];
        //        //strDim = strDim + " = " + ds.Tables[2].Rows[0]["TABLE_NAME"] + "." + DvCol[0]["FIELD_NAME"];
        //        //strDim = strDim + " WHERE " + ds.Tables[2].Rows[0]["TABLE_NAME"] + "." + DvCol[0]["FIELD_NAME"] + " IS NULL";

        //        //SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('M_PRODUCTS')
        //        strDim = strDim + " SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('" + ds.Tables[0].Rows[0]["TABLE_NAME"];
        //        strDim = strDim + " ')";

        //        //strDim = strDim + " SELECT * FROM " + ds.Tables[0].Rows[0]["TABLE_NAME"];
        //        //strDim = strDim + " WHERE 1=0";

        //        DataSet dsData = sqlConn.GetDataSetFromFactory(strDim);
        //        DataTable dt = dsData.Tables[0];
        //        DataTable dtColnm = dsData.Tables[1];

        //        //actual data
        //        List<object> rows = new List<object>();
        //        foreach (DataRow dr in dt.Rows)
        //        {
        //            Dictionary<string, object> row = new Dictionary<string, object>();
        //            foreach (DataRow drcol in dtColnm.Rows)
        //            {
        //                row.Add(drcol["name"].ToString(), dr[drcol["name"].ToString()]);
        //            }

        //            rows.Add(row);
        //        }

        //        griddata.datarow = rows;


        //        if (bOnlydata == false)
        //        {

        //            int index = 0;
        //            List<colModel> listColmodel = new List<colModel>();
        //            List<string> columns = new List<string>();

        //            //
        //            foreach (DataRow dr in ds.Tables[1].Rows)
        //            {

        //                //for columns
        //                columns.Add(dr["DISPLAY"].ToString());
        //                //for colmodel
        //                colModel colmodel = new colModel();

        //                colmodel.index = dr["FIELD_NAME"].ToString();
        //                colmodel.name = dr["FIELD_NAME"].ToString();

        //                Dictionary<string, object> edoptions = new Dictionary<string, object>();
        //                Dictionary<string, object> editrules = new Dictionary<string, object>();
        //                Dictionary<string, object> formatoptions = new Dictionary<string, object>();

        //                if (dr["DATA_TYPE"].ToString().ToLower() == "number") //integer for whole number
        //                {
        //                    colmodel.sorttype = "int";
        //                    colmodel.align = "right";
        //                    editrules.Add("number", true);
        //                    edoptions.Add("NullIfEmpty", true);
        //                    //colmodel.NullIfEmpty = "true";                        
        //                    //formatter 
        //                    colmodel.formatter = "number";
        //                    //format option for number
        //                    formatoptions.Add("decimalSeparator", ".");
        //                    formatoptions.Add("defaulValue", 0);
        //                    formatoptions.Add("decimalPlaces", 2);
        //                }
        //                else if (dr["DATA_TYPE"].ToString().ToLower() == "varchar")
        //                {
        //                    colmodel.sorttype = "text";
        //                    colmodel.align = "left";
        //                    //formatter
        //                    // colmodel.formatter = "NullFormatter";//defaultFormat
        //                    colmodel.formatter = "defaultFormat";
        //                }
        //                else if (dr["DATA_TYPE"].ToString().ToLower() == "date")
        //                {
        //                    colmodel.sorttype = "date";
        //                    colmodel.align = "right";

        //                    editrules.Add("date", true);
        //                    colmodel.formatter = "date";
        //                    ////date format in mm/dd/yyyy
        //                    formatoptions.Add("srcformat", "ISO8601Long");
        //                    formatoptions.Add("newformat", "m/d/Y");

        //                }
        //                else if (dr["DATA_TYPE"].ToString().ToLower() == "float")
        //                {
        //                    colmodel.sorttype = "float";
        //                    colmodel.align = "right";
        //                }

        //                //add key to col model
        //                if (dr["IS_PRIMARY"].ToString().ToLower() == "true")
        //                {
        //                    colmodel.key = true;
        //                }


        //                listColmodel.Add(colmodel);
        //                index++;
        //            }

        //            primarykey = DvCol[0]["FIELD_NAME"].ToString();
        //            griddata.colModelList = listColmodel;
        //            griddata.columns = columns;
        //            griddata.primarykey = primarykey;
        //        }

        //        //JavaScriptSerializer js = new JavaScriptSerializer();
        //        //return js.Serialize(griddata);
        //        return griddata;

        //    }
        //    catch (Exception ex)
        //    {
        //        throw ex;
        //    }
        //}


        // get unassigned grid data

        public Grid GetDimGriddataUn(Boolean bOnlydata)
        {
            try
            {

                Grid griddata = new Grid();
                griddata = GetjqgridCol(bOnlydata, "unassigned");
                return griddata;

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "GetDimGriddataUn", "GetDimGriddataUn", "", ex);
                throw ex;
            }
        }


        public Grid GetjqgridCol(Boolean bOnlydata, string type)
        {

            try
            {

                string primarykey = string.Empty;
                Grid griddata = new Grid();

                Util.WritInfoLog(ClsName, "GetjqgridCol", "Start Creating Connection");
                Connection sqlConn = new Connection();
                Util.WritInfoLog(ClsName, "GetjqgridCol", "End Creating Connection");

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@HierarchyId", DbType.Int32, ParameterDirection.Input, 0, HierarchyId));
                lstparam.Add(new DBParameter("@dimname", DbType.String, ParameterDirection.Input, 0, dimname));

                Util.WritInfoLog(ClsName, "GetjqgridCol", "method of usp_GetjqgridCol", string.Format("method:{0}", "usp_GetjqgridCol"));
                DataSet ds = sqlConn.GetDataSetFromFactoryProc("usp_GetjqgridCol", ref(lstparam));
                Util.WritInfoLog(ClsName, "GetjqgridCol", "End method of usp_GetjqgridCol");

                DataTable dtCol = ds.Tables[1];
                DataView DvCol = new DataView(dtCol);
                DataView DvColPrimary = new DataView(dtCol);

                DvColPrimary.RowFilter = " IS_PRIMARY =TRUE";
                StringBuilder SB = new StringBuilder();
                foreach (DataRowView dr in DvCol)
                {
                    SB.Append("DIM." + dr["FIELD_NAME"].ToString() + ", ");
                }
                string strCols = SB.ToString().Substring(0, SB.ToString().Length - 2);
                string strRelTblName = string.Empty;
                if (ds.Tables[2].Rows.Count > 0)
                {
                    strRelTblName = ds.Tables[2].Rows[0][0].ToString();
                }


                if (type == "exist")
                {
                    DvCol.RowFilter = " IS_PRIMARY =FALSE";
                }

                if (bOnlydata == false)
                {

                    int index = 0;
                    List<colModel> listColmodel = new List<colModel>();
                    List<string> columns = new List<string>();
                    //
                    if (type == "exist")
                    {
                        columns.Add("REL_ID");
                        colModel colmodel1 = new colModel();
                        colmodel1.index = "REL_ID";
                        colmodel1.name = "REL_ID";
                        colmodel1.key = true;
                        listColmodel.Add(colmodel1);
                    }
                    //

                    //foreach (DataRow dr in ds.Tables[1].Rows)
                    foreach (DataRowView dr in DvCol)
                    {

                        //for columns
                        columns.Add(dr["DISPLAY"].ToString());
                        //for colmodel
                        colModel colmodel = new colModel();

                        colmodel.index = dr["FIELD_NAME"].ToString();
                        colmodel.name = dr["FIELD_NAME"].ToString();

                        Dictionary<string, object> edoptions = new Dictionary<string, object>();
                        Dictionary<string, object> editrules = new Dictionary<string, object>();
                        Dictionary<string, object> formatoptions = new Dictionary<string, object>();

                        if (dr["DATA_TYPE"].ToString().ToLower() == "number") //integer for whole number
                        {
                            colmodel.sorttype = "int";
                            colmodel.align = "right";
                            editrules.Add("number", true);
                            edoptions.Add("NullIfEmpty", true);

                            //formatter 
                            colmodel.formatter = "number";
                            //format option for number
                            formatoptions.Add("decimalSeparator", ".");
                            formatoptions.Add("defaulValue", 0);
                            formatoptions.Add("decimalPlaces", 2);
                        }
                        else if (dr["DATA_TYPE"].ToString().ToLower() == "varchar")
                        {
                            colmodel.sorttype = "string";
                            colmodel.align = "left";
                            //formatter                           
                            colmodel.formatter = "defaultFormat";
                        }
                        else if (dr["DATA_TYPE"].ToString().ToLower() == "date")
                        {
                            colmodel.sorttype = "date";
                            colmodel.align = "right";
                            editrules.Add("date", true);
                            colmodel.formatter = "date";
                            ////date format in mm/dd/yyyy
                            formatoptions.Add("srcformat", "ISO8601Long");
                            formatoptions.Add("newformat", "m/d/Y");
                        }
                        else if (dr["DATA_TYPE"].ToString().ToLower() == "float")
                        {
                            colmodel.sorttype = "float";
                            colmodel.align = "right";
                        }

                        //add key to col model
                        if (dr["IS_PRIMARY"].ToString().ToLower() == "true")
                        {
                            colmodel.key = true;
                        }

                        if (dr["DISPLAY"].ToString() == "Id")
                        {
                            colmodel.hidden = true;
                        }

                        listColmodel.Add(colmodel);
                        index++;
                    }

                    if (type == "exist")
                    {
                        primarykey = "REL_ID";
                    }
                    else
                    {
                        DvCol.RowFilter = " IS_PRIMARY =TRUE";
                        primarykey = DvCol[0]["FIELD_NAME"].ToString();
                    }

                    griddata.colModelList = listColmodel;
                    griddata.columns = columns;
                    griddata.primarykey = primarykey;

                }

                return griddata;

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "GetjqgridCol", "GetjqgridCol", "", ex);
                throw ex;
            }
        }


        //before SP
        //Fill jqgrid colmodel and columns and data 
        //for exist,un-assigned and add relation data
        //public Grid GetjqgridCol(Boolean bOnlydata, string type)
        //{

        //    try
        //    {

        //        string primarykey = string.Empty;
        //        Grid griddata = new Grid();

        //        Util.WritInfoLog(ClsName, "GetjqgridCol", "Start Creating Connection");
        //        Connection sqlConn = new Connection();
        //        Util.WritInfoLog(ClsName, "GetjqgridCol", "End Creating Connection");

        //        string strQry = " SELECT * FROM X_MASTER_LIST WHERE TABLE_ABBR= '" + dimname + "'";

        //        strQry = strQry + " SELECT * FROM X_MASTER_LIST_COLUMNS ";
        //        strQry = strQry + " WHERE LIST_ID= (SELECT LIST_ID FROM X_MASTER_LIST ";
        //        strQry = strQry + " WHERE TABLE_ABBR=  '" + dimname + "')";

        //        strQry = strQry + " SELECT X_HIER_MASTER_LIST_REL.TABLE_NAME FROM X_HIER_MASTER_LIST_REL ";
        //        strQry = strQry + " INNER JOIN X_MASTER_LIST ON ";
        //        strQry = strQry + " X_HIER_MASTER_LIST_REL.MASTER_LIST_ID =X_MASTER_LIST.LIST_ID ";
        //        strQry = strQry + " WHERE HIERARCHY_ID= " + HierarchyId;
        //        strQry = strQry + " AND TABLE_ABBR= '" + dimname + "'";

        //        DataSet ds = sqlConn.GetDataSetFromFactory(strQry);
        //        DataTable dtCol = ds.Tables[1];
        //        DataView DvCol = new DataView(dtCol);
        //        DataView DvColPrimary = new DataView(dtCol);

        //        DvColPrimary.RowFilter = " IS_PRIMARY =TRUE";
        //        StringBuilder SB = new StringBuilder();
        //        foreach (DataRowView dr in DvCol)
        //        {
        //            SB.Append("DIM." + dr["FIELD_NAME"].ToString() + ", ");
        //        }
        //        string strCols = SB.ToString().Substring(0, SB.ToString().Length - 2);
        //        string strRelTblName = string.Empty;
        //        if (ds.Tables[2].Rows.Count > 0)
        //        {
        //            strRelTblName = ds.Tables[2].Rows[0][0].ToString();
        //        }

        //        //string strDim = string.Empty;
        //        //if (type == "unassigned")
        //        //{

        //        //    //not exist 
        //        //    strDim = " SELECT * FROM " + ds.Tables[0].Rows[0]["TABLE_NAME"];
        //        //    strDim = strDim + " WHERE NOT EXISTS ( SELECT  NULL FROM " + ds.Tables[2].Rows[0]["TABLE_NAME"];
        //        //    strDim = strDim + " WHERE " + ds.Tables[0].Rows[0]["TABLE_NAME"] + "." + DvCol[0]["FIELD_NAME"];
        //        //    strDim = strDim + " = " + ds.Tables[2].Rows[0]["TABLE_NAME"] + "." + DvCol[0]["FIELD_NAME"];
        //        //    strDim = strDim + " AND CONVERT(DATE,END_DATE,101) >= '" + txtDate + "'";
        //        //    strDim = strDim + " AND CONVERT(DATE,START_DATE,101) <= '" + txtDate + "')";
        //        //}
        //        //else if (type == "exist")
        //        //{

        //        //    strDim = " SELECT * FROM " + ds.Tables[0].Rows[0]["TABLE_NAME"];
        //        //    strDim = strDim + " INNER JOIN " + ds.Tables[2].Rows[0]["TABLE_NAME"];
        //        //    strDim = strDim + " ON " + ds.Tables[0].Rows[0]["TABLE_NAME"] + "." + DvCol[0]["FIELD_NAME"];
        //        //    strDim = strDim + " = " + ds.Tables[2].Rows[0]["TABLE_NAME"] + "." + DvCol[0]["FIELD_NAME"];
        //        //    strDim = strDim + " WHERE NODE_ID= " + NodeId;
        //        //    //for change logic of no date checking and new add relation logic
        //        //    // strDim = strDim + " AND CONVERT(DATE,END_DATE,101) >= '" + txtDate + "'";
        //        //    // strDim = strDim + " AND CONVERT(DATE,START_DATE,101) <= '" + txtDate + "'";

        //        //}
        //        //else if (type == "addrel")
        //        //{

        //        //    //to change a/c to new rule
        //        //    //strDim = " SELECT * FROM " + ds.Tables[0].Rows[0]["TABLE_NAME"];
        //        //    //strDim = strDim + " WHERE NOT EXISTS ( SELECT " + DvCol[0]["FIELD_NAME"].ToString() + " FROM " + ds.Tables[2].Rows[0]["TABLE_NAME"];
        //        //    //strDim = strDim + " WHERE " + ds.Tables[0].Rows[0]["TABLE_NAME"] + "." + DvCol[0]["FIELD_NAME"];
        //        //    //strDim = strDim + " = " + ds.Tables[2].Rows[0]["TABLE_NAME"] + "." + DvCol[0]["FIELD_NAME"];
        //        //    //strDim = strDim + " AND CONVERT(DATE,END_DATE,101) >= '" + txtDate + "'";
        //        //    //strDim = strDim + " AND CONVERT(DATE,START_DATE,101) <= '" + txtDate + "'";
        //        //    //strDim = strDim + " AND NODE_ID = " + NodeId + ")";

        //        //    // NEW LOGIC FOR GETTING ANCESTORS DIMESION RECORDS 
        //        //    strDim = " WITH Hierarchy(Node_id,Node_name, Parent_ID, HLevel) AS ( ";
        //        //    strDim = strDim + " SELECT Node_id, [Node_name], Parent_ID , 0 AS HLevel FROM " + TableName;
        //        //    strDim = strDim + " WHERE NODE_ID = ( SELECT Parent_ID FROM " + TableName + " WHERE NODE_ID = " + NodeId + " ) ";
        //        //    strDim = strDim + " UNION ALL ";
        //        //    strDim = strDim + " SELECT  parent.Node_id, parent.[Node_name], parent.Parent_ID , HLevel + 1 ";
        //        //    strDim = strDim + " FROM " + TableName + " parent ";
        //        //    strDim = strDim + " INNER JOIN Hierarchy ON parent.Node_id = Hierarchy.Parent_ID ) ";

        //        //    //strDim = strDim + " SELECT " + strCols + " FROM Hierarchy HER ";
        //        //    strDim = strDim + " SELECT DIM.* FROM Hierarchy HER ";
        //        //    strDim = strDim + " INNER JOIN " + strRelTblName + " REL ON REL.NODE_ID = HER.Node_id ";
        //        //    strDim = strDim + " INNER JOIN " + ds.Tables[0].Rows[0]["TABLE_NAME"] + " DIM ON DIM." + DvCol[0]["FIELD_NAME"] + "= REL." + DvCol[0]["FIELD_NAME"];
        //        //    strDim = strDim + " ORDER BY HLevel DESC ";
        //        //}

        //        ////get column names of table
        //        //strDim = strDim + " SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('" + ds.Tables[0].Rows[0]["TABLE_NAME"] + "')";

        //        //  DataSet dsData = sqlConn.GetDataSetFromFactory(strDim);
        //        //  DataTable dtData = dsData.Tables[0];
        //        // DataTable dtColnm = dsData.Tables[1];

        //        if (type == "exist")
        //        {
        //            DvCol.RowFilter = " IS_PRIMARY =FALSE";
        //        }

        //        ////actual data
        //        //List<object> rows = new List<object>();
        //        //foreach (DataRow dr in dtData.Rows)
        //        //{

        //        //    Dictionary<string, object> row = new Dictionary<string, object>();
        //        //    if (type == "exist")
        //        //    {
        //        //        row.Add("REL_ID", dr["REL_ID"]);
        //        //    }
        //        //    foreach (DataRow drcol in dtColnm.Rows)
        //        //    {
        //        //        row.Add(drcol["name"].ToString(), dr[drcol["name"].ToString()]);
        //        //    }
        //        //    rows.Add(row);
        //        //}

        //        //griddata.datarow = rows;

        //        if (bOnlydata == false)
        //        {

        //            int index = 0;
        //            List<colModel> listColmodel = new List<colModel>();
        //            List<string> columns = new List<string>();
        //            //
        //            if (type == "exist")
        //            {
        //                columns.Add("REL_ID");
        //                colModel colmodel1 = new colModel();
        //                colmodel1.index = "REL_ID";
        //                colmodel1.name = "REL_ID";
        //                colmodel1.key = true;
        //                listColmodel.Add(colmodel1);
        //            }
        //            //

        //            //foreach (DataRow dr in ds.Tables[1].Rows)
        //            foreach (DataRowView dr in DvCol)
        //            {

        //                //for columns
        //                columns.Add(dr["DISPLAY"].ToString());
        //                //for colmodel
        //                colModel colmodel = new colModel();

        //                colmodel.index = dr["FIELD_NAME"].ToString();
        //                colmodel.name = dr["FIELD_NAME"].ToString();

        //                Dictionary<string, object> edoptions = new Dictionary<string, object>();
        //                Dictionary<string, object> editrules = new Dictionary<string, object>();
        //                Dictionary<string, object> formatoptions = new Dictionary<string, object>();

        //                if (dr["DATA_TYPE"].ToString().ToLower() == "number") //integer for whole number
        //                {
        //                    colmodel.sorttype = "int";
        //                    colmodel.align = "right";
        //                    editrules.Add("number", true);
        //                    edoptions.Add("NullIfEmpty", true);

        //                    //formatter 
        //                    colmodel.formatter = "number";
        //                    //format option for number
        //                    formatoptions.Add("decimalSeparator", ".");
        //                    formatoptions.Add("defaulValue", 0);
        //                    formatoptions.Add("decimalPlaces", 2);
        //                }
        //                else if (dr["DATA_TYPE"].ToString().ToLower() == "varchar")
        //                {
        //                    colmodel.sorttype = "string";
        //                    colmodel.align = "left";
        //                    //formatter                           
        //                    colmodel.formatter = "defaultFormat";
        //                }
        //                else if (dr["DATA_TYPE"].ToString().ToLower() == "date")
        //                {
        //                    colmodel.sorttype = "date";
        //                    colmodel.align = "right";
        //                    editrules.Add("date", true);
        //                    colmodel.formatter = "date";
        //                    ////date format in mm/dd/yyyy
        //                    formatoptions.Add("srcformat", "ISO8601Long");
        //                    formatoptions.Add("newformat", "m/d/Y");
        //                }
        //                else if (dr["DATA_TYPE"].ToString().ToLower() == "float")
        //                {
        //                    colmodel.sorttype = "float";
        //                    colmodel.align = "right";
        //                }

        //                //add key to col model
        //                if (dr["IS_PRIMARY"].ToString().ToLower() == "true")
        //                {
        //                    colmodel.key = true;
        //                }

        //                if (dr["DISPLAY"].ToString() == "Id")
        //                {
        //                    colmodel.hidden = true;
        //                }

        //                listColmodel.Add(colmodel);
        //                index++;
        //            }                   

        //            if (type == "exist")
        //            {
        //                primarykey = "REL_ID";
        //            }
        //            else
        //            {
        //                DvCol.RowFilter = " IS_PRIMARY =TRUE";
        //                primarykey = DvCol[0]["FIELD_NAME"].ToString();
        //            }

        //            griddata.colModelList = listColmodel;
        //            griddata.columns = columns;
        //            griddata.primarykey = primarykey;

        //        }

        //        return griddata;

        //    }
        //    catch (Exception ex)
        //    {
        //        Util.WritErrorLog(ClsName, "GetjqgridCol", "GetjqgridCol", "", ex);
        //        throw ex;
        //    }
        //}


        public string GetjqgridData(string type)
        {

            try
            {

                string primarykey = string.Empty;
                Grid griddata = new Grid();

                Util.WritInfoLog(ClsName, "GetjqgridData", "Start Creating Connection");
                Connection sqlConn = new Connection();
                Util.WritInfoLog(ClsName, "GetjqgridData", "End Creating Connection");

                List<DBParameter> lstparam = new List<DBParameter>();
                int start = ((page - 1) * rows);              

                lstparam.Add(new DBParameter("@dimname", DbType.String, ParameterDirection.Input, 255, dimname));
                lstparam.Add(new DBParameter("@TableName", DbType.String, ParameterDirection.Input, 255, TableName));
                lstparam.Add(new DBParameter("@HierarchyId", DbType.Int32, ParameterDirection.Input, 0, HierarchyId));

                lstparam.Add(new DBParameter("@type", DbType.String, ParameterDirection.Input, 255, type));
                lstparam.Add(new DBParameter("@txtDate", DbType.DateTime, ParameterDirection.Input, 0, txtDate));//NodeId
                lstparam.Add(new DBParameter("@NodeId", DbType.Int32, ParameterDirection.Input, 0, NodeId));//NodeId

                lstparam.Add(new DBParameter("@start", DbType.Int32, ParameterDirection.Input, 0, start));
                lstparam.Add(new DBParameter("@rows", DbType.Int32, ParameterDirection.Input, 0, rows));
                lstparam.Add(new DBParameter("@_search", DbType.Boolean, ParameterDirection.Input, 0, _search));

                lstparam.Add(new DBParameter("@query", DbType.String, ParameterDirection.Input, 1000, query.Replace("\"", "'")));
                lstparam.Add(new DBParameter("@sidx", DbType.String, ParameterDirection.Input, 255, sidx));
                lstparam.Add(new DBParameter("@sord", DbType.String, ParameterDirection.Input, 255, sord));
                lstparam.Add(new DBParameter("@page", DbType.Int32, ParameterDirection.Input, 0, page));
                // lstparam.Add(new DBParameter("@sortExp", DbType.String, ParameterDirection.Input, 1000, sortExp));

                Util.WritInfoLog(ClsName, "GetjqgridData", "method for GetjqgridData");
                DataSet dsData = sqlConn.GetDataSetFromFactoryProc("usp_GetjqgridData", ref(lstparam));
                Util.WritInfoLog(ClsName, "GetjqgridData", "End method for GetjqgridData");

                DataTable dtData = dsData.Tables[0];
                DataTable dtColnm = dsData.Tables[2];

                DataTable dtCol = dsData.Tables[3];
                DataView DvCol = new DataView(dtCol);

                if (type == "exist")
                {
                    dtData.TableName = "exist";
                    DvCol.RowFilter = " IS_PRIMARY =FALSE";
                    //dtColnm.re
                    dtColnm = DvCol.ToTable();
                }
                else
                {
                    dtColnm = DvCol.ToTable();
                }


                int cnt = 0;
                cnt = Convert.ToInt32(dsData.Tables[1].Rows[0]["CNT"]);
                string strData = Common.JsonForJqgrid(dtData, dtColnm, rows, cnt, page);
                return strData;

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "GetjqgridCol", "GetjqgridCol", "", ex);
                throw ex;
            }
        }


        //before SP
        public string GetjqgridData1(string type)
        {
            try
            {

                string primarykey = string.Empty;
                Grid griddata = new Grid();

                Util.WritInfoLog(ClsName, "GetjqgridData", "Start Creating Connection");
                Connection sqlConn = new Connection();
                Util.WritInfoLog(ClsName, "GetjqgridData", "End Creating Connection");

                string strQry = " SELECT * FROM X_MASTER_LIST WHERE TABLE_ABBR= '" + dimname + "'";

                strQry = strQry + " SELECT * FROM X_MASTER_LIST_COLUMNS ";
                strQry = strQry + " WHERE LIST_ID= (SELECT LIST_ID FROM X_MASTER_LIST ";
                strQry = strQry + " WHERE TABLE_ABBR=  '" + dimname + "')";

                strQry = strQry + " SELECT X_HIER_MASTER_LIST_REL.TABLE_NAME FROM X_HIER_MASTER_LIST_REL ";
                strQry = strQry + " INNER JOIN X_MASTER_LIST ON ";
                strQry = strQry + " X_HIER_MASTER_LIST_REL.MASTER_LIST_ID =X_MASTER_LIST.LIST_ID ";
                strQry = strQry + " WHERE HIERARCHY_ID= " + HierarchyId;
                strQry = strQry + " AND TABLE_ABBR= '" + dimname + "'";

                Util.WritInfoLog(ClsName, "GetjqgridData", "Query for getting details in GetjqgridData", string.Format("Query:{0}", strQry));
                DataSet ds = sqlConn.GetDataSetFromFactory(strQry);
                Util.WritInfoLog(ClsName, "GetjqgridData", "End Query for getting details in GetjqgridData");

                DataTable dtCol = ds.Tables[1];
                DataView DvCol = new DataView(dtCol);
                DataView DvColPrimary = new DataView(dtCol);

                DvColPrimary.RowFilter = " IS_PRIMARY =TRUE";
                // DvCol.RowFilter = " IS_PRIMARY =TRUE";

                StringBuilder SB = new StringBuilder();
                foreach (DataRowView dr in DvCol)
                {
                    SB.Append("DIM." + dr["FIELD_NAME"].ToString() + ", ");
                }

                string strCols = SB.ToString().Substring(0, SB.ToString().Length - 2);
                string strRelTblName = string.Empty;
                if (ds.Tables[2].Rows.Count > 0)
                {
                    strRelTblName = ds.Tables[2].Rows[0][0].ToString();
                }

                //for url jqgrid
                //if (sidx == "" || sidx == null)
                //{
                //    sidx =Convert.ToString(DvCol[0]["FIELD_NAME"]);
                //}
                //string sortExp = sidx + " " + sord;
                //int start = ((page - 1) * rows);
                //
                string strDim = string.Empty;
                if (type == "unassigned")
                {

                    //not exist 
                    strDim = " SELECT * FROM " + ds.Tables[0].Rows[0]["TABLE_NAME"];
                    strDim = strDim + " WHERE NOT EXISTS ( SELECT  NULL FROM " + ds.Tables[2].Rows[0]["TABLE_NAME"];
                    strDim = strDim + " WHERE " + ds.Tables[0].Rows[0]["TABLE_NAME"] + "." + DvCol[0]["FIELD_NAME"];
                    strDim = strDim + " = " + ds.Tables[2].Rows[0]["TABLE_NAME"] + "." + DvCol[0]["FIELD_NAME"];
                    strDim = strDim + " AND CONVERT(DATE,END_DATE,101) >= '" + txtDate + "'";
                    strDim = strDim + " AND CONVERT(DATE,START_DATE,101) <= '" + txtDate + "')";
                }
                else if (type == "exist")
                {

                    if (sidx == "" || sidx == null)
                    {
                        sidx = ds.Tables[0].Rows[0]["TABLE_NAME"] + "." + Convert.ToString(DvColPrimary[0]["FIELD_NAME"]);
                    }
                    string sortExp = sidx + " " + sord;
                    int start = ((page - 1) * rows);

                    ////original query
                    //strDim = " SELECT * FROM " + ds.Tables[0].Rows[0]["TABLE_NAME"];
                    //strDim = strDim + " INNER JOIN " + ds.Tables[2].Rows[0]["TABLE_NAME"];
                    //strDim = strDim + " ON " + ds.Tables[0].Rows[0]["TABLE_NAME"] + "." + DvCol[0]["FIELD_NAME"];
                    //strDim = strDim + " = " + ds.Tables[2].Rows[0]["TABLE_NAME"] + "." + DvCol[0]["FIELD_NAME"];
                    //strDim = strDim + " WHERE NODE_ID= " + NodeId;
                    ////for change logic of no date checking and new add relation logic
                    //// strDim = strDim + " AND CONVERT(DATE,END_DATE,101) >= '" + txtDate + "'";
                    //// strDim = strDim + " AND CONVERT(DATE,START_DATE,101) <= '" + txtDate + "'";

                    //strDim = " SELECT * FROM " + ds.Tables[0].Rows[0]["TABLE_NAME"];
                    //strDim = strDim + " INNER JOIN " + ds.Tables[2].Rows[0]["TABLE_NAME"];
                    //strDim = strDim + " ON " + ds.Tables[0].Rows[0]["TABLE_NAME"] + "." + DvCol[0]["FIELD_NAME"];
                    //strDim = strDim + " = " + ds.Tables[2].Rows[0]["TABLE_NAME"] + "." + DvCol[0]["FIELD_NAME"];
                    //strDim = strDim + " WHERE NODE_ID= " + NodeId;
                    //strDim = strDim + " ORDER BY  " + sortExp;                    

                    //changed query for paging of jqgrid
                    strDim = " SELECT * FROM  ";
                    strDim = strDim + " (SELECT ROW_NUMBER() OVER (ORDER BY " + sortExp + " ) AS Row,";
                    strDim = strDim + ds.Tables[0].Rows[0]["TABLE_NAME"] + ".* ," + ds.Tables[2].Rows[0]["TABLE_NAME"] + ".REL_ID";
                    strDim = strDim + " FROM " + ds.Tables[0].Rows[0]["TABLE_NAME"];
                    strDim = strDim + "  INNER JOIN " + ds.Tables[2].Rows[0]["TABLE_NAME"];
                    strDim = strDim + " ON " + ds.Tables[0].Rows[0]["TABLE_NAME"] + "." + DvColPrimary[0]["FIELD_NAME"];
                    strDim = strDim + " = " + ds.Tables[2].Rows[0]["TABLE_NAME"] + "." + DvColPrimary[0]["FIELD_NAME"];
                    strDim = strDim + " WHERE NODE_ID= " + NodeId;
                    //for search filter
                    if (_search && (query != null || query != ""))
                    {
                        strDim = strDim + " AND " + query.Replace("\"", "'");//errString.Replace("docment", "document");
                    }

                    strDim = strDim + "  )tmp  ";
                    strDim = strDim + " WHERE Row > " + start + " AND Row <= " + (start + rows);

                    //strDim =strDim +  " SELECT * ";
                    //strDim = strDim + " FROM (SELECT ROW_NUMBER() OVER (ORDER BY " + sortExp + ") AS Row,";
                    //strDim = strDim + " * FROM  X_MASTER_LIST ) tmp ";
                    //strDim = strDim + " WHERE Row > " + start + " AND Row <= " + (start + rows);


                    //for count
                    //strDim = strDim + " SELECT COUNT(*) CNT FROM " + ds.Tables[0].Rows[0]["TABLE_NAME"];
                    //strDim = strDim + " INNER JOIN " + ds.Tables[2].Rows[0]["TABLE_NAME"];
                    //strDim = strDim + " ON " + ds.Tables[0].Rows[0]["TABLE_NAME"] + "." + DvCol[0]["FIELD_NAME"];
                    //strDim = strDim + " = " + ds.Tables[2].Rows[0]["TABLE_NAME"] + "." + DvCol[0]["FIELD_NAME"];
                    //strDim = strDim + " WHERE NODE_ID= " + NodeId;
                    strDim = strDim + " SELECT COUNT(*) CNT FROM  ";
                    strDim = strDim + " (SELECT ROW_NUMBER() OVER (ORDER BY " + sortExp + " ) AS Row,";
                    strDim = strDim + ds.Tables[0].Rows[0]["TABLE_NAME"] + ".* ," + ds.Tables[2].Rows[0]["TABLE_NAME"] + ".REL_ID";
                    strDim = strDim + " FROM " + ds.Tables[0].Rows[0]["TABLE_NAME"];
                    strDim = strDim + "  INNER JOIN " + ds.Tables[2].Rows[0]["TABLE_NAME"];
                    strDim = strDim + " ON " + ds.Tables[0].Rows[0]["TABLE_NAME"] + "." + DvColPrimary[0]["FIELD_NAME"];
                    strDim = strDim + " = " + ds.Tables[2].Rows[0]["TABLE_NAME"] + "." + DvColPrimary[0]["FIELD_NAME"];
                    strDim = strDim + " WHERE NODE_ID= " + NodeId;
                    //for search filter
                    if (_search && (query != null || query != ""))
                    {
                        strDim = strDim + " AND " + query.Replace("\"", "'");//errString.Replace("docment", "document");
                    }
                    strDim = strDim + "  )tmp  ";

                }
                else if (type == "addrel")
                {

                    //if (sidx == "" || sidx == null)
                    //{
                    //    sidx = Convert.ToString(DvCol[0]["FIELD_NAME"]);
                    //}

                    string sortExp = " HLevel DESC ";
                    if (sidx != "" && sidx != null)
                    {
                        //sortExp = sortExp +" ,"  + sidx + " " + sord;
                    }

                    int start = ((page - 1) * rows);

                    //to change a/c to new rule
                    //strDim = " SELECT * FROM " + ds.Tables[0].Rows[0]["TABLE_NAME"];
                    //strDim = strDim + " WHERE NOT EXISTS ( SELECT " + DvCol[0]["FIELD_NAME"].ToString() + " FROM " + ds.Tables[2].Rows[0]["TABLE_NAME"];
                    //strDim = strDim + " WHERE " + ds.Tables[0].Rows[0]["TABLE_NAME"] + "." + DvCol[0]["FIELD_NAME"];
                    //strDim = strDim + " = " + ds.Tables[2].Rows[0]["TABLE_NAME"] + "." + DvCol[0]["FIELD_NAME"];
                    //strDim = strDim + " AND CONVERT(DATE,END_DATE,101) >= '" + txtDate + "'";
                    //strDim = strDim + " AND CONVERT(DATE,START_DATE,101) <= '" + txtDate + "'";
                    //strDim = strDim + " AND NODE_ID = " + NodeId + ")";

                    //// NEW LOGIC FOR GETTING ANCESTORS DIMESION RECORDS
                    //original query before url jqgrid

                    //strDim = " WITH Hierarchy(Node_id,Node_name, Parent_ID, HLevel) AS ( ";
                    //strDim = strDim + " SELECT Node_id, [Node_name], Parent_ID , 0 AS HLevel FROM " + TableName;
                    //strDim = strDim + " WHERE NODE_ID = ( SELECT Parent_ID FROM " + TableName + " WHERE NODE_ID = " + NodeId + " ) ";
                    //strDim = strDim + " UNION ALL ";
                    //strDim = strDim + " SELECT  parent.Node_id, parent.[Node_name], parent.Parent_ID , HLevel + 1 ";
                    //strDim = strDim + " FROM " + TableName + " parent ";
                    //strDim = strDim + " INNER JOIN Hierarchy ON parent.Node_id = Hierarchy.Parent_ID ) ";
                    //strDim = strDim + " SELECT DIM.* FROM Hierarchy HER ";
                    //strDim = strDim + " INNER JOIN " + strRelTblName + " REL ON REL.NODE_ID = HER.Node_id ";
                    //strDim = strDim + " INNER JOIN " + ds.Tables[0].Rows[0]["TABLE_NAME"] + " DIM ON DIM." + DvCol[0]["FIELD_NAME"] + "= REL." + DvCol[0]["FIELD_NAME"];
                    //strDim = strDim + " ORDER BY HLevel DESC ";

                    // NEW LOGIC FOR GETTING ANCESTORS DIMESION RECORDS 
                    strDim = " WITH Hierarchy(Node_id,Node_name, Parent_ID, HLevel) AS ( ";
                    strDim = strDim + " SELECT Node_id, [Node_name], Parent_ID , 0 AS HLevel FROM " + TableName;
                    strDim = strDim + " WHERE NODE_ID = ( SELECT Parent_ID FROM " + TableName + " WHERE NODE_ID = " + NodeId + " ) ";
                    strDim = strDim + " UNION ALL ";
                    strDim = strDim + " SELECT  parent.Node_id, parent.[Node_name], parent.Parent_ID , HLevel + 1 ";
                    strDim = strDim + " FROM " + TableName + " parent ";
                    strDim = strDim + " INNER JOIN Hierarchy ON parent.Node_id = Hierarchy.Parent_ID ) ";

                    strDim = strDim + " SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY " + sortExp + ") AS Row, ";
                    strDim = strDim + "  DIM.* FROM Hierarchy HER ";
                    strDim = strDim + " INNER JOIN " + strRelTblName + " REL ON REL.NODE_ID = HER.Node_id ";
                    strDim = strDim + " INNER JOIN " + ds.Tables[0].Rows[0]["TABLE_NAME"];
                    strDim = strDim + " DIM ON DIM." + DvColPrimary[0]["FIELD_NAME"] + "= REL." + DvColPrimary[0]["FIELD_NAME"];
                    //for search filter
                    if (_search && (query != null || query != ""))
                    {
                        strDim = strDim + " WHERE " + query.Replace("\"", "'");//errString.Replace("docment", "document");
                    }
                    strDim = strDim + "  )tmp  ";
                    strDim = strDim + " WHERE Row > " + start + " AND Row <= " + (start + rows);
                    strDim = strDim + " ;";
                    //strDim = strDim + " ORDER BY HLevel DESC ";

                    //for count
                    // strDim = strDim + " SELECT COUNT(*) CNT FROM " + TableName;
                    strDim = strDim + " WITH Hierarchy(Node_id,Node_name, Parent_ID, HLevel) AS ( ";
                    strDim = strDim + " SELECT Node_id, [Node_name], Parent_ID , 0 AS HLevel FROM " + TableName;
                    strDim = strDim + " WHERE NODE_ID = ( SELECT Parent_ID FROM " + TableName + " WHERE NODE_ID = " + NodeId + " ) ";
                    strDim = strDim + " UNION ALL ";
                    strDim = strDim + " SELECT  parent.Node_id, parent.[Node_name], parent.Parent_ID , HLevel + 1 ";
                    strDim = strDim + " FROM " + TableName + " parent ";
                    strDim = strDim + " INNER JOIN Hierarchy ON parent.Node_id = Hierarchy.Parent_ID ) ";

                    strDim = strDim + " SELECT COUNT(*) CNT FROM (SELECT ROW_NUMBER() OVER (ORDER BY " + sortExp + ") AS Row, ";
                    strDim = strDim + "  DIM.* FROM Hierarchy HER ";
                    strDim = strDim + " INNER JOIN " + strRelTblName + " REL ON REL.NODE_ID = HER.Node_id ";
                    strDim = strDim + " INNER JOIN " + ds.Tables[0].Rows[0]["TABLE_NAME"];
                    strDim = strDim + " DIM ON DIM." + DvColPrimary[0]["FIELD_NAME"] + "= REL." + DvColPrimary[0]["FIELD_NAME"];
                    //for search filter
                    if (_search && (query != null || query != ""))
                    {
                        strDim = strDim + " WHERE " + query.Replace("\"", "'");//errString.Replace("docment", "document");
                    }
                    strDim = strDim + "  )tmp ; ";

                }

                //get column names of table
                strDim = strDim + " SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('" + ds.Tables[0].Rows[0]["TABLE_NAME"] + "')";
                strDim = strDim + " ;";


                Util.WritInfoLog(ClsName, "GetTypes", "Query for GetjqgridData", string.Format("Query:{0}", strDim));
                DataSet dsData = sqlConn.GetDataSetFromFactory(strDim);
                Util.WritInfoLog(ClsName, "GetTypes", "End Query for GetjqgridData");

                DataTable dtData = dsData.Tables[0];
                DataTable dtColnm = dsData.Tables[2];

                if (type == "exist")
                {
                    dtData.TableName = "exist";
                    DvCol.RowFilter = " IS_PRIMARY =FALSE";
                    //dtColnm.re
                    dtColnm = DvCol.ToTable();
                }
                else
                {
                    dtColnm = DvCol.ToTable();
                }


                int cnt = 0;
                cnt = Convert.ToInt32(dsData.Tables[1].Rows[0]["CNT"]);
                string strData = Common.JsonForJqgrid(dtData, dtColnm, rows, cnt, page);
                return strData;

                ////actual data
                //List<object> rows = new List<object>();
                //foreach (DataRow dr in dtData.Rows)
                //{

                //    Dictionary<string, object> row = new Dictionary<string, object>();
                //    if (type == "exist")
                //    {
                //        row.Add("REL_ID", dr["REL_ID"]);
                //    }
                //    foreach (DataRow drcol in dtColnm.Rows)
                //    {
                //        row.Add(drcol["name"].ToString(), dr[drcol["name"].ToString()]);
                //    }
                //    rows.Add(row);
                //}

                //griddata.datarow = rows;   
                //return "true";

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "GetjqgridCol", "GetjqgridCol", "", ex);
                throw ex;
            }
        }


        //before SP
        //public string GetjqgridData(string type)
        //{

        //    try
        //    {

        //        string primarykey = string.Empty;
        //        Grid griddata = new Grid();

        //        Util.WritInfoLog(ClsName, "GetjqgridData", "Start Creating Connection");
        //        Connection sqlConn = new Connection();
        //        Util.WritInfoLog(ClsName, "GetjqgridData", "End Creating Connection");

        //        string strQry = " SELECT * FROM X_MASTER_LIST WHERE TABLE_ABBR= '" + dimname + "'";

        //        strQry = strQry + " SELECT * FROM X_MASTER_LIST_COLUMNS ";
        //        strQry = strQry + " WHERE LIST_ID= (SELECT LIST_ID FROM X_MASTER_LIST ";
        //        strQry = strQry + " WHERE TABLE_ABBR=  '" + dimname + "')";

        //        strQry = strQry + " SELECT X_HIER_MASTER_LIST_REL.TABLE_NAME FROM X_HIER_MASTER_LIST_REL ";
        //        strQry = strQry + " INNER JOIN X_MASTER_LIST ON ";
        //        strQry = strQry + " X_HIER_MASTER_LIST_REL.MASTER_LIST_ID =X_MASTER_LIST.LIST_ID ";
        //        strQry = strQry + " WHERE HIERARCHY_ID= " + HierarchyId;
        //        strQry = strQry + " AND TABLE_ABBR= '" + dimname + "'";

        //        Util.WritInfoLog(ClsName, "GetjqgridData", "Query for getting details in GetjqgridData", string.Format("Query:{0}", strQry));
        //        DataSet ds = sqlConn.GetDataSetFromFactory(strQry);
        //        Util.WritInfoLog(ClsName, "GetjqgridData", "End Query for getting details in GetjqgridData");

        //        DataTable dtCol = ds.Tables[1];
        //        DataView DvCol = new DataView(dtCol);
        //        DataView DvColPrimary = new DataView(dtCol);

        //        DvColPrimary.RowFilter = " IS_PRIMARY =TRUE";
        //        // DvCol.RowFilter = " IS_PRIMARY =TRUE";

        //        StringBuilder SB = new StringBuilder();
        //        foreach (DataRowView dr in DvCol)
        //        {
        //            SB.Append("DIM." + dr["FIELD_NAME"].ToString() + ", ");
        //        }

        //        string strCols = SB.ToString().Substring(0, SB.ToString().Length - 2);
        //        string strRelTblName = string.Empty;
        //        if (ds.Tables[2].Rows.Count > 0)
        //        {
        //            strRelTblName = ds.Tables[2].Rows[0][0].ToString();
        //        }

        //        //for url jqgrid
        //        //if (sidx == "" || sidx == null)
        //        //{
        //        //    sidx =Convert.ToString(DvCol[0]["FIELD_NAME"]);
        //        //}
        //        //string sortExp = sidx + " " + sord;
        //        //int start = ((page - 1) * rows);
        //        //
        //        string strDim = string.Empty;
        //        if (type == "unassigned")
        //        {

        //            //not exist 
        //            strDim = " SELECT * FROM " + ds.Tables[0].Rows[0]["TABLE_NAME"];
        //            strDim = strDim + " WHERE NOT EXISTS ( SELECT  NULL FROM " + ds.Tables[2].Rows[0]["TABLE_NAME"];
        //            strDim = strDim + " WHERE " + ds.Tables[0].Rows[0]["TABLE_NAME"] + "." + DvCol[0]["FIELD_NAME"];
        //            strDim = strDim + " = " + ds.Tables[2].Rows[0]["TABLE_NAME"] + "." + DvCol[0]["FIELD_NAME"];
        //            strDim = strDim + " AND CONVERT(DATE,END_DATE,101) >= '" + txtDate + "'";
        //            strDim = strDim + " AND CONVERT(DATE,START_DATE,101) <= '" + txtDate + "')";
        //        }
        //        else if (type == "exist")
        //        {

        //            if (sidx == "" || sidx == null)
        //            {
        //                sidx = ds.Tables[0].Rows[0]["TABLE_NAME"] + "." + Convert.ToString(DvColPrimary[0]["FIELD_NAME"]);
        //            }
        //            string sortExp = sidx + " " + sord;
        //            int start = ((page - 1) * rows);

        //            ////original query
        //            //strDim = " SELECT * FROM " + ds.Tables[0].Rows[0]["TABLE_NAME"];
        //            //strDim = strDim + " INNER JOIN " + ds.Tables[2].Rows[0]["TABLE_NAME"];
        //            //strDim = strDim + " ON " + ds.Tables[0].Rows[0]["TABLE_NAME"] + "." + DvCol[0]["FIELD_NAME"];
        //            //strDim = strDim + " = " + ds.Tables[2].Rows[0]["TABLE_NAME"] + "." + DvCol[0]["FIELD_NAME"];
        //            //strDim = strDim + " WHERE NODE_ID= " + NodeId;
        //            ////for change logic of no date checking and new add relation logic
        //            //// strDim = strDim + " AND CONVERT(DATE,END_DATE,101) >= '" + txtDate + "'";
        //            //// strDim = strDim + " AND CONVERT(DATE,START_DATE,101) <= '" + txtDate + "'";

        //            //strDim = " SELECT * FROM " + ds.Tables[0].Rows[0]["TABLE_NAME"];
        //            //strDim = strDim + " INNER JOIN " + ds.Tables[2].Rows[0]["TABLE_NAME"];
        //            //strDim = strDim + " ON " + ds.Tables[0].Rows[0]["TABLE_NAME"] + "." + DvCol[0]["FIELD_NAME"];
        //            //strDim = strDim + " = " + ds.Tables[2].Rows[0]["TABLE_NAME"] + "." + DvCol[0]["FIELD_NAME"];
        //            //strDim = strDim + " WHERE NODE_ID= " + NodeId;
        //            //strDim = strDim + " ORDER BY  " + sortExp;                    

        //            //changed query for paging of jqgrid
        //            strDim = " SELECT * FROM  ";
        //            strDim = strDim + " (SELECT ROW_NUMBER() OVER (ORDER BY " + sortExp + " ) AS Row,";
        //            strDim = strDim + ds.Tables[0].Rows[0]["TABLE_NAME"] + ".* ," + ds.Tables[2].Rows[0]["TABLE_NAME"] + ".REL_ID";
        //            strDim = strDim + " FROM " + ds.Tables[0].Rows[0]["TABLE_NAME"];
        //            strDim = strDim + "  INNER JOIN " + ds.Tables[2].Rows[0]["TABLE_NAME"];
        //            strDim = strDim + " ON " + ds.Tables[0].Rows[0]["TABLE_NAME"] + "." + DvColPrimary[0]["FIELD_NAME"];
        //            strDim = strDim + " = " + ds.Tables[2].Rows[0]["TABLE_NAME"] + "." + DvColPrimary[0]["FIELD_NAME"];
        //            strDim = strDim + " WHERE NODE_ID= " + NodeId;
        //            //for search filter
        //            if (_search && (query != null || query != ""))
        //            {
        //                strDim = strDim + " AND " + query.Replace("\"", "'");//errString.Replace("docment", "document");
        //            }

        //            strDim = strDim + "  )tmp  ";
        //            strDim = strDim + " WHERE Row > " + start + " AND Row <= " + (start + rows);

        //            //strDim =strDim +  " SELECT * ";
        //            //strDim = strDim + " FROM (SELECT ROW_NUMBER() OVER (ORDER BY " + sortExp + ") AS Row,";
        //            //strDim = strDim + " * FROM  X_MASTER_LIST ) tmp ";
        //            //strDim = strDim + " WHERE Row > " + start + " AND Row <= " + (start + rows);


        //            //for count
        //            //strDim = strDim + " SELECT COUNT(*) CNT FROM " + ds.Tables[0].Rows[0]["TABLE_NAME"];
        //            //strDim = strDim + " INNER JOIN " + ds.Tables[2].Rows[0]["TABLE_NAME"];
        //            //strDim = strDim + " ON " + ds.Tables[0].Rows[0]["TABLE_NAME"] + "." + DvCol[0]["FIELD_NAME"];
        //            //strDim = strDim + " = " + ds.Tables[2].Rows[0]["TABLE_NAME"] + "." + DvCol[0]["FIELD_NAME"];
        //            //strDim = strDim + " WHERE NODE_ID= " + NodeId;
        //            strDim = strDim + " SELECT COUNT(*) CNT FROM  ";
        //            strDim = strDim + " (SELECT ROW_NUMBER() OVER (ORDER BY " + sortExp + " ) AS Row,";
        //            strDim = strDim + ds.Tables[0].Rows[0]["TABLE_NAME"] + ".* ," + ds.Tables[2].Rows[0]["TABLE_NAME"] + ".REL_ID";
        //            strDim = strDim + " FROM " + ds.Tables[0].Rows[0]["TABLE_NAME"];
        //            strDim = strDim + "  INNER JOIN " + ds.Tables[2].Rows[0]["TABLE_NAME"];
        //            strDim = strDim + " ON " + ds.Tables[0].Rows[0]["TABLE_NAME"] + "." + DvColPrimary[0]["FIELD_NAME"];
        //            strDim = strDim + " = " + ds.Tables[2].Rows[0]["TABLE_NAME"] + "." + DvColPrimary[0]["FIELD_NAME"];
        //            strDim = strDim + " WHERE NODE_ID= " + NodeId;
        //            //for search filter
        //            if (_search && (query != null || query != ""))
        //            {
        //                strDim = strDim + " AND " + query.Replace("\"", "'");//errString.Replace("docment", "document");
        //            }
        //            strDim = strDim + "  )tmp  ";

        //        }
        //        else if (type == "addrel")
        //        {

        //            //if (sidx == "" || sidx == null)
        //            //{
        //            //    sidx = Convert.ToString(DvCol[0]["FIELD_NAME"]);
        //            //}

        //            string sortExp = " HLevel DESC ";
        //            if (sidx != "" && sidx != null)
        //            {
        //                //sortExp = sortExp +" ,"  + sidx + " " + sord;
        //            }

        //            int start = ((page - 1) * rows);

        //            //to change a/c to new rule
        //            //strDim = " SELECT * FROM " + ds.Tables[0].Rows[0]["TABLE_NAME"];
        //            //strDim = strDim + " WHERE NOT EXISTS ( SELECT " + DvCol[0]["FIELD_NAME"].ToString() + " FROM " + ds.Tables[2].Rows[0]["TABLE_NAME"];
        //            //strDim = strDim + " WHERE " + ds.Tables[0].Rows[0]["TABLE_NAME"] + "." + DvCol[0]["FIELD_NAME"];
        //            //strDim = strDim + " = " + ds.Tables[2].Rows[0]["TABLE_NAME"] + "." + DvCol[0]["FIELD_NAME"];
        //            //strDim = strDim + " AND CONVERT(DATE,END_DATE,101) >= '" + txtDate + "'";
        //            //strDim = strDim + " AND CONVERT(DATE,START_DATE,101) <= '" + txtDate + "'";
        //            //strDim = strDim + " AND NODE_ID = " + NodeId + ")";

        //            //// NEW LOGIC FOR GETTING ANCESTORS DIMESION RECORDS
        //            //original query before url jqgrid

        //            //strDim = " WITH Hierarchy(Node_id,Node_name, Parent_ID, HLevel) AS ( ";
        //            //strDim = strDim + " SELECT Node_id, [Node_name], Parent_ID , 0 AS HLevel FROM " + TableName;
        //            //strDim = strDim + " WHERE NODE_ID = ( SELECT Parent_ID FROM " + TableName + " WHERE NODE_ID = " + NodeId + " ) ";
        //            //strDim = strDim + " UNION ALL ";
        //            //strDim = strDim + " SELECT  parent.Node_id, parent.[Node_name], parent.Parent_ID , HLevel + 1 ";
        //            //strDim = strDim + " FROM " + TableName + " parent ";
        //            //strDim = strDim + " INNER JOIN Hierarchy ON parent.Node_id = Hierarchy.Parent_ID ) ";
        //            //strDim = strDim + " SELECT DIM.* FROM Hierarchy HER ";
        //            //strDim = strDim + " INNER JOIN " + strRelTblName + " REL ON REL.NODE_ID = HER.Node_id ";
        //            //strDim = strDim + " INNER JOIN " + ds.Tables[0].Rows[0]["TABLE_NAME"] + " DIM ON DIM." + DvCol[0]["FIELD_NAME"] + "= REL." + DvCol[0]["FIELD_NAME"];
        //            //strDim = strDim + " ORDER BY HLevel DESC ";

        //            // NEW LOGIC FOR GETTING ANCESTORS DIMESION RECORDS 
        //            strDim = " WITH Hierarchy(Node_id,Node_name, Parent_ID, HLevel) AS ( ";
        //            strDim = strDim + " SELECT Node_id, [Node_name], Parent_ID , 0 AS HLevel FROM " + TableName;
        //            strDim = strDim + " WHERE NODE_ID = ( SELECT Parent_ID FROM " + TableName + " WHERE NODE_ID = " + NodeId + " ) ";
        //            strDim = strDim + " UNION ALL ";
        //            strDim = strDim + " SELECT  parent.Node_id, parent.[Node_name], parent.Parent_ID , HLevel + 1 ";
        //            strDim = strDim + " FROM " + TableName + " parent ";
        //            strDim = strDim + " INNER JOIN Hierarchy ON parent.Node_id = Hierarchy.Parent_ID ) ";

        //            strDim = strDim + " SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY " + sortExp + ") AS Row, ";
        //            strDim = strDim + "  DIM.* FROM Hierarchy HER ";
        //            strDim = strDim + " INNER JOIN " + strRelTblName + " REL ON REL.NODE_ID = HER.Node_id ";
        //            strDim = strDim + " INNER JOIN " + ds.Tables[0].Rows[0]["TABLE_NAME"];
        //            strDim = strDim + " DIM ON DIM." + DvColPrimary[0]["FIELD_NAME"] + "= REL." + DvColPrimary[0]["FIELD_NAME"];
        //            //for search filter
        //            if (_search && (query != null || query != ""))
        //            {
        //                strDim = strDim + " WHERE " + query.Replace("\"", "'");//errString.Replace("docment", "document");
        //            }
        //            strDim = strDim + "  )tmp  ";
        //            strDim = strDim + " WHERE Row > " + start + " AND Row <= " + (start + rows);
        //            strDim = strDim + " ;";
        //            //strDim = strDim + " ORDER BY HLevel DESC ";

        //            //for count
        //            // strDim = strDim + " SELECT COUNT(*) CNT FROM " + TableName;
        //            strDim = strDim + " WITH Hierarchy(Node_id,Node_name, Parent_ID, HLevel) AS ( ";
        //            strDim = strDim + " SELECT Node_id, [Node_name], Parent_ID , 0 AS HLevel FROM " + TableName;
        //            strDim = strDim + " WHERE NODE_ID = ( SELECT Parent_ID FROM " + TableName + " WHERE NODE_ID = " + NodeId + " ) ";
        //            strDim = strDim + " UNION ALL ";
        //            strDim = strDim + " SELECT  parent.Node_id, parent.[Node_name], parent.Parent_ID , HLevel + 1 ";
        //            strDim = strDim + " FROM " + TableName + " parent ";
        //            strDim = strDim + " INNER JOIN Hierarchy ON parent.Node_id = Hierarchy.Parent_ID ) ";

        //            strDim = strDim + " SELECT COUNT(*) CNT FROM (SELECT ROW_NUMBER() OVER (ORDER BY " + sortExp + ") AS Row, ";
        //            strDim = strDim + "  DIM.* FROM Hierarchy HER ";
        //            strDim = strDim + " INNER JOIN " + strRelTblName + " REL ON REL.NODE_ID = HER.Node_id ";
        //            strDim = strDim + " INNER JOIN " + ds.Tables[0].Rows[0]["TABLE_NAME"];
        //            strDim = strDim + " DIM ON DIM." + DvColPrimary[0]["FIELD_NAME"] + "= REL." + DvColPrimary[0]["FIELD_NAME"];
        //            //for search filter
        //            if (_search && (query != null || query != ""))
        //            {
        //                strDim = strDim + " WHERE " + query.Replace("\"", "'");//errString.Replace("docment", "document");
        //            }
        //            strDim = strDim + "  )tmp ; ";

        //        }

        //        //get column names of table
        //        strDim = strDim + " SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('" + ds.Tables[0].Rows[0]["TABLE_NAME"] + "')";
        //        strDim = strDim + " ;";


        //        Util.WritInfoLog(ClsName, "GetTypes", "Query for GetjqgridData", string.Format("Query:{0}", strDim));
        //        DataSet dsData = sqlConn.GetDataSetFromFactory(strDim);
        //        Util.WritInfoLog(ClsName, "GetTypes", "End Query for GetjqgridData");

        //        DataTable dtData = dsData.Tables[0];
        //        DataTable dtColnm = dsData.Tables[2];

        //        if (type == "exist")
        //        {
        //            dtData.TableName = "exist";
        //            DvCol.RowFilter = " IS_PRIMARY =FALSE";
        //            //dtColnm.re
        //            dtColnm = DvCol.ToTable();
        //        }
        //        else
        //        {
        //            dtColnm = DvCol.ToTable();
        //        }


        //        int cnt = 0;
        //        cnt = Convert.ToInt32(dsData.Tables[1].Rows[0]["CNT"]);
        //        string strData = Common.JsonForJqgrid(dtData, dtColnm, rows, cnt, page);
        //        return strData;

        //        ////actual data
        //        //List<object> rows = new List<object>();
        //        //foreach (DataRow dr in dtData.Rows)
        //        //{

        //        //    Dictionary<string, object> row = new Dictionary<string, object>();
        //        //    if (type == "exist")
        //        //    {
        //        //        row.Add("REL_ID", dr["REL_ID"]);
        //        //    }
        //        //    foreach (DataRow drcol in dtColnm.Rows)
        //        //    {
        //        //        row.Add(drcol["name"].ToString(), dr[drcol["name"].ToString()]);
        //        //    }
        //        //    rows.Add(row);
        //        //}

        //        //griddata.datarow = rows;   
        //        //return "true";

        //    }
        //    catch (Exception ex)
        //    {
        //        Util.WritErrorLog(ClsName, "GetjqgridCol", "GetjqgridCol", "", ex);
        //        throw ex;
        //    }
        //}

        //original before url for jqgrid
        //public Grid GetjqgridCol(Boolean bOnlydata, string type)
        //{

        //    try
        //    {

        //        string primarykey = string.Empty;
        //        Grid griddata = new Grid();

        //        Connection sqlConn = new Connection();
        //        string strQry = " SELECT * FROM X_MASTER_LIST WHERE TABLE_ABBR= '" + dimname + "'";

        //        strQry = strQry + " SELECT * FROM X_MASTER_LIST_COLUMNS ";
        //        strQry = strQry + " WHERE LIST_ID= (SELECT LIST_ID FROM X_MASTER_LIST ";
        //        strQry = strQry + " WHERE TABLE_ABBR=  '" + dimname + "')";

        //        strQry = strQry + " SELECT X_HIER_MASTER_LIST_REL.TABLE_NAME FROM X_HIER_MASTER_LIST_REL ";
        //        strQry = strQry + " INNER JOIN X_MASTER_LIST ON ";
        //        strQry = strQry + " X_HIER_MASTER_LIST_REL.MASTER_LIST_ID =X_MASTER_LIST.LIST_ID ";
        //        strQry = strQry + " WHERE HIERARCHY_ID= " + HierarchyId;
        //        strQry = strQry + " AND TABLE_ABBR= '" + dimname + "'";

        //        DataSet ds = sqlConn.GetDataSetFromFactory(strQry);
        //        DataTable dtCol = ds.Tables[1];                
        //        DataView DvCol = new DataView(dtCol);                
        //        DataView DvColPrimary = new DataView(dtCol);

        //        DvColPrimary.RowFilter = " IS_PRIMARY =TRUE";
        //        StringBuilder SB = new StringBuilder();
        //        foreach (DataRowView dr in DvCol)
        //        {
        //            SB.Append("DIM." + dr["FIELD_NAME"].ToString() + ", ");
        //        }               
        //        string strCols = SB.ToString().Substring(0, SB.ToString().Length - 2);
        //        string strRelTblName = string.Empty;
        //        if (ds.Tables[2].Rows.Count > 0)
        //        {
        //            strRelTblName = ds.Tables[2].Rows[0][0].ToString();
        //        }

        //        string strDim = string.Empty;
        //        if (type == "unassigned")
        //        {

        //            //not exist 
        //            strDim = " SELECT * FROM " + ds.Tables[0].Rows[0]["TABLE_NAME"];
        //            strDim = strDim + " WHERE NOT EXISTS ( SELECT  NULL FROM " + ds.Tables[2].Rows[0]["TABLE_NAME"];
        //            strDim = strDim + " WHERE " + ds.Tables[0].Rows[0]["TABLE_NAME"] + "." + DvCol[0]["FIELD_NAME"];
        //            strDim = strDim + " = " + ds.Tables[2].Rows[0]["TABLE_NAME"] + "." + DvCol[0]["FIELD_NAME"];
        //            strDim = strDim + " AND CONVERT(DATE,END_DATE,101) >= '" + txtDate + "'";
        //            strDim = strDim + " AND CONVERT(DATE,START_DATE,101) <= '" + txtDate + "')";
        //        }
        //        else if (type == "exist")
        //        {

        //            strDim = " SELECT * FROM " + ds.Tables[0].Rows[0]["TABLE_NAME"];
        //            strDim = strDim + " INNER JOIN " + ds.Tables[2].Rows[0]["TABLE_NAME"];
        //            strDim = strDim + " ON " + ds.Tables[0].Rows[0]["TABLE_NAME"] + "." + DvCol[0]["FIELD_NAME"];
        //            strDim = strDim + " = " + ds.Tables[2].Rows[0]["TABLE_NAME"] + "." + DvCol[0]["FIELD_NAME"];
        //            strDim = strDim + " WHERE NODE_ID= " + NodeId;
        //            //for change logic of no date checking and new add relation logic
        //            // strDim = strDim + " AND CONVERT(DATE,END_DATE,101) >= '" + txtDate + "'";
        //            // strDim = strDim + " AND CONVERT(DATE,START_DATE,101) <= '" + txtDate + "'";

        //        }
        //        else if (type == "addrel")
        //        {

        //            //to change a/c to new rule
        //            //strDim = " SELECT * FROM " + ds.Tables[0].Rows[0]["TABLE_NAME"];
        //            //strDim = strDim + " WHERE NOT EXISTS ( SELECT " + DvCol[0]["FIELD_NAME"].ToString() + " FROM " + ds.Tables[2].Rows[0]["TABLE_NAME"];
        //            //strDim = strDim + " WHERE " + ds.Tables[0].Rows[0]["TABLE_NAME"] + "." + DvCol[0]["FIELD_NAME"];
        //            //strDim = strDim + " = " + ds.Tables[2].Rows[0]["TABLE_NAME"] + "." + DvCol[0]["FIELD_NAME"];
        //            //strDim = strDim + " AND CONVERT(DATE,END_DATE,101) >= '" + txtDate + "'";
        //            //strDim = strDim + " AND CONVERT(DATE,START_DATE,101) <= '" + txtDate + "'";
        //            //strDim = strDim + " AND NODE_ID = " + NodeId + ")";

        //            // NEW LOGIC FOR GETTING ANCESTORS DIMESION RECORDS 
        //            strDim = " WITH Hierarchy(Node_id,Node_name, Parent_ID, HLevel) AS ( ";
        //            strDim = strDim + " SELECT Node_id, [Node_name], Parent_ID , 0 AS HLevel FROM " + TableName;
        //            strDim = strDim + " WHERE NODE_ID = ( SELECT Parent_ID FROM " + TableName + " WHERE NODE_ID = " + NodeId + " ) ";
        //            strDim = strDim + " UNION ALL ";
        //            strDim = strDim + " SELECT  parent.Node_id, parent.[Node_name], parent.Parent_ID , HLevel + 1 ";
        //            strDim = strDim + " FROM " + TableName + " parent ";
        //            strDim = strDim + " INNER JOIN Hierarchy ON parent.Node_id = Hierarchy.Parent_ID ) ";

        //            //strDim = strDim + " SELECT " + strCols + " FROM Hierarchy HER ";
        //            strDim = strDim + " SELECT DIM.* FROM Hierarchy HER ";
        //            strDim = strDim + " INNER JOIN " + strRelTblName + " REL ON REL.NODE_ID = HER.Node_id ";
        //            strDim = strDim + " INNER JOIN " + ds.Tables[0].Rows[0]["TABLE_NAME"] + " DIM ON DIM." + DvCol[0]["FIELD_NAME"] + "= REL." + DvCol[0]["FIELD_NAME"];
        //            strDim = strDim + " ORDER BY HLevel DESC ";
        //        }

        //        //get column names of table
        //        strDim = strDim + " SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('" + ds.Tables[0].Rows[0]["TABLE_NAME"] + "')";

        //        DataSet dsData = sqlConn.GetDataSetFromFactory(strDim);
        //        DataTable dtData = dsData.Tables[0];
        //        DataTable dtColnm = dsData.Tables[1];

        //        if (type == "exist")
        //        {
        //            DvCol.RowFilter = " IS_PRIMARY =FALSE";
        //        }

        //        //actual data
        //        List<object> rows = new List<object>();
        //        foreach (DataRow dr in dtData.Rows)
        //        {

        //            Dictionary<string, object> row = new Dictionary<string, object>();
        //            if (type == "exist")
        //            {
        //                row.Add("REL_ID", dr["REL_ID"]);
        //            }
        //            foreach (DataRow drcol in dtColnm.Rows)
        //            {
        //                row.Add(drcol["name"].ToString(), dr[drcol["name"].ToString()]);
        //            }
        //            rows.Add(row);
        //        }

        //        griddata.datarow = rows;

        //        if (bOnlydata == false)
        //        {

        //            int index = 0;
        //            List<colModel> listColmodel = new List<colModel>();
        //            List<string> columns = new List<string>();
        //            //
        //            if (type == "exist")
        //            {
        //                columns.Add("REL_ID");
        //                colModel colmodel1 = new colModel();
        //                colmodel1.index = "REL_ID";
        //                colmodel1.name = "REL_ID";
        //                colmodel1.key = true;
        //                listColmodel.Add(colmodel1);
        //            }
        //            //

        //            //foreach (DataRow dr in ds.Tables[1].Rows)
        //            foreach (DataRowView dr in DvCol)
        //            {

        //                //for columns
        //                columns.Add(dr["DISPLAY"].ToString());
        //                //for colmodel
        //                colModel colmodel = new colModel();

        //                colmodel.index = dr["FIELD_NAME"].ToString();
        //                colmodel.name = dr["FIELD_NAME"].ToString();

        //                Dictionary<string, object> edoptions = new Dictionary<string, object>();
        //                Dictionary<string, object> editrules = new Dictionary<string, object>();
        //                Dictionary<string, object> formatoptions = new Dictionary<string, object>();

        //                if (dr["DATA_TYPE"].ToString().ToLower() == "number") //integer for whole number
        //                {
        //                    colmodel.sorttype = "int";
        //                    colmodel.align = "right";
        //                    editrules.Add("number", true);
        //                    edoptions.Add("NullIfEmpty", true);

        //                    //formatter 
        //                    colmodel.formatter = "number";
        //                    //format option for number
        //                    formatoptions.Add("decimalSeparator", ".");
        //                    formatoptions.Add("defaulValue", 0);
        //                    formatoptions.Add("decimalPlaces", 2);
        //                }
        //                else if (dr["DATA_TYPE"].ToString().ToLower() == "varchar")
        //                {
        //                    colmodel.sorttype = "string";
        //                    colmodel.align = "left";
        //                    //formatter                           
        //                    colmodel.formatter = "defaultFormat";
        //                }
        //                else if (dr["DATA_TYPE"].ToString().ToLower() == "date")
        //                {
        //                    colmodel.sorttype = "date";
        //                    colmodel.align = "right";
        //                    editrules.Add("date", true);
        //                    colmodel.formatter = "date";
        //                    ////date format in mm/dd/yyyy
        //                    formatoptions.Add("srcformat", "ISO8601Long");
        //                    formatoptions.Add("newformat", "m/d/Y");
        //                }
        //                else if (dr["DATA_TYPE"].ToString().ToLower() == "float")
        //                {
        //                    colmodel.sorttype = "float";
        //                    colmodel.align = "right";
        //                }

        //                //add key to col model
        //                if (dr["IS_PRIMARY"].ToString().ToLower() == "true")
        //                {
        //                    colmodel.key = true;
        //                }

        //                listColmodel.Add(colmodel);
        //                index++;
        //            }

        //            if (type == "exist")
        //            {
        //                primarykey = "REL_ID";
        //            }
        //            else
        //            {
        //                DvCol.RowFilter = " IS_PRIMARY =TRUE";
        //                primarykey = DvCol[0]["FIELD_NAME"].ToString();
        //            }

        //            griddata.colModelList = listColmodel;
        //            griddata.columns = columns;
        //            griddata.primarykey = primarykey;

        //        }

        //        return griddata;

        //    }
        //    catch (Exception ex)
        //    {
        //        Util.WritErrorLog(ClsName, "GetjqgridCol", "GetjqgridCol", "", ex);
        //        throw ex;
        //    }
        //}


        public void RemoveRel()
        {
            try
            {

                Util.WritInfoLog(ClsName, "RemoveRel", "Start Creating Connection");
                Connection sqlConn = new Connection();
                Util.WritInfoLog(ClsName, "RemoveRel", "End Creating Connection");

                var xmlDocument = new XmlDocument();
                var serializer = new XmlSerializer(typeof(String[]));

                using (var stream = new MemoryStream())
                {
                    serializer.Serialize(stream, arrRelId);
                    stream.Flush(); stream.Seek(0, SeekOrigin.Begin);
                    xmlDocument.Load(stream);
                }

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@HierarchyId", DbType.Int32, ParameterDirection.Input, 0, HierarchyId));
                lstparam.Add(new DBParameter("@ID_XML", DbType.Xml, ParameterDirection.Input, 0, xmlDocument.OuterXml));
                lstparam.Add(new DBParameter("@dimname", DbType.String, ParameterDirection.Input, 255, dimname));

                Util.WritInfoLog(ClsName, "RemoveRel", "method of usp_RemoveRel", string.Format("method:{0}", "usp_RemoveRel"));
                Int32 ret = sqlConn.SaveDataByFactoryByProc("usp_RemoveRel", ref(lstparam));
                Util.WritInfoLog(ClsName, "RemoveRel", "End method of usp_RemoveRel");

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "RemoveRel", "RemoveRel", "", ex);
                throw ex;
            }
        }

        //before SP
        //remove relation of dimensions
        //public void RemoveRel()
        //{
        //    try
        //    {

        //        Util.WritInfoLog(ClsName, "RemoveRel", "Start Creating Connection");
        //        Connection sqlConn = new Connection();
        //        Util.WritInfoLog(ClsName, "RemoveRel", "End Creating Connection");

        //        string strQry = " SELECT X_HIER_MASTER_LIST_REL.TABLE_NAME FROM X_HIER_MASTER_LIST_REL ";
        //        strQry = strQry + " INNER JOIN X_MASTER_LIST ON ";
        //        strQry = strQry + " X_HIER_MASTER_LIST_REL.MASTER_LIST_ID =X_MASTER_LIST.LIST_ID ";
        //        strQry = strQry + " WHERE HIERARCHY_ID= " + HierarchyId;
        //        strQry = strQry + " AND TABLE_ABBR= '" + dimname + "'";

        //        Util.WritInfoLog(ClsName, "RemoveRel", "Query for getting relation table name", string.Format("Query:{0}", strQry));
        //        DataSet ds = sqlConn.GetDataSetFromFactory(strQry);
        //        Util.WritInfoLog(ClsName, "RemoveRel", "End Query for getting relation table name");

        //        for (int i = 0; i < arrRelId.Length; i++)
        //        {
        //            //update end_date to node which we have to remove
        //            string strQryUpd = " UPDATE " + ds.Tables[0].Rows[0]["TABLE_NAME"] + " SET NODE_ID = 1 ";
        //            strQryUpd = strQryUpd + " WHERE REL_ID = " + arrRelId[i];

        //            Util.WritInfoLog(ClsName, "RemoveRel", "Query for RemoveRel", string.Format("Query:{0}", strQryUpd));
        //            Int32 ret = sqlConn.SaveDataByFactory(strQryUpd);
        //            Util.WritInfoLog(ClsName, "RemoveRel", "End Query for RemoveRel");
        //        }

        //    }
        //    catch (Exception ex)
        //    {
        //        Util.WritErrorLog(ClsName, "RemoveRel", "RemoveRel", "", ex);
        //        throw ex;
        //    }
        //}


        public string AddRel()
        {
            try
            {

                Util.WritInfoLog(ClsName, "AddRel", "Start Creating Connection");
                Connection sqlConn = new Connection();
                Util.WritInfoLog(ClsName, "AddRel", "End Creating Connection");

                var xmlDocument = new XmlDocument();
                var serializer = new XmlSerializer(typeof(String[]));


                using (var stream = new MemoryStream())
                {
                    serializer.Serialize(stream, arrRelId);
                    stream.Flush(); stream.Seek(0, SeekOrigin.Begin);
                    xmlDocument.Load(stream);
                }

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@ID_XML", DbType.Xml, ParameterDirection.Input, 0, xmlDocument.OuterXml));
                lstparam.Add(new DBParameter("@HierarchyId", DbType.Int32, ParameterDirection.Input, 0, HierarchyId));
                lstparam.Add(new DBParameter("@VersionId", DbType.Int32, ParameterDirection.Input, 0, VersionId));
                lstparam.Add(new DBParameter("@NodeId", DbType.Int32, ParameterDirection.Input, 0, NodeId));
                lstparam.Add(new DBParameter("@TableName", DbType.String, ParameterDirection.Input, 255, TableName));
                lstparam.Add(new DBParameter("@dimname", DbType.String, ParameterDirection.Input, 255, dimname));
                lstparam.Add(new DBParameter("@txtDate", DbType.DateTime, ParameterDirection.Input, 255, txtDate));

                Util.WritInfoLog(ClsName, "AddRel", "method for AddRel", "usp_AddRel");
                Int32 res = sqlConn.SaveDataByFactoryByProc("usp_AddRel", ref(lstparam));
                Util.WritInfoLog(ClsName, "AddRel", "End method for AddRel");

                return "true";
                        
            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "AddRel", "AddRel", "", ex);
                return "false";
                throw ex;
            }
        }

        //before SP
        //add relation of dimensions of un-assigned data
        //public void AddRel()
        //{
        //    try
        //    {

        //        Util.WritInfoLog(ClsName, "AddRel", "Start Creating Connection");
        //        Connection sqlConn = new Connection();
        //        Util.WritInfoLog(ClsName, "AddRel", "End Creating Connection");

        //        string strQry = " SELECT * FROM X_MASTER_LIST_COLUMNS ";
        //        strQry = strQry + " WHERE LIST_ID= (SELECT LIST_ID FROM X_MASTER_LIST ";
        //        strQry = strQry + " WHERE TABLE_ABBR=  '" + dimname + "')";
        //        strQry = strQry + " AND IS_PRIMARY='TRUE'";

        //        strQry = strQry + " SELECT X_HIER_MASTER_LIST_REL.TABLE_NAME FROM X_HIER_MASTER_LIST_REL ";
        //        strQry = strQry + " INNER JOIN X_MASTER_LIST ON ";
        //        strQry = strQry + " X_HIER_MASTER_LIST_REL.MASTER_LIST_ID =X_MASTER_LIST.LIST_ID ";
        //        strQry = strQry + " WHERE HIERARCHY_ID= " + HierarchyId;
        //        strQry = strQry + " AND TABLE_ABBR= '" + dimname + "'";

        //        //get end date of that hier_id and ver_id  -1
        //        strQry = strQry + " SELECT END_DATE FROM X_HIER_VERSIONS ";
        //        strQry = strQry + " WHERE HIERARCHY_ID = " + HierarchyId;
        //        strQry = strQry + " AND VERSION_ID = " + VersionId;


        //        Util.WritInfoLog(ClsName, "AddRel", "Query for get details in AddRel", string.Format("Query:{0}", strQry));
        //        DataSet ds = sqlConn.GetDataSetFromFactory(strQry);
        //        Util.WritInfoLog(ClsName, "AddRel", "End Query for get details in AddRel");

        //        if (ds.Tables[1].Rows.Count > 0)
        //        {
        //            EndDate = Convert.ToDateTime(ds.Tables[2].Rows[0]["END_DATE"]);
        //        }

        //        string strQryMax = " SELECT ISNULL(MAX(REL_ID),0) AS REL_ID FROM " + ds.Tables[1].Rows[0]["TABLE_NAME"];
        //        DataSet dsMax = sqlConn.GetDataSetFromFactory(strQryMax);

        //        DataTable dtCol = ds.Tables[0];
        //        DataView DvCol = new DataView(dtCol);
        //        DvCol.RowFilter = " IS_PRIMARY =TRUE ";
        //        Int64 intRelID = Convert.ToInt64(dsMax.Tables[0].Rows[0]["REL_ID"]);

        //        for (int i = 0; i < arrRelId.Length; i++)
        //        {

        //            //insert relation 
        //            string strQryIns = string.Empty;
        //            //strQryIns = strQryIns + " INSERT INTO " + ds.Tables[1].Rows[0]["TABLE_NAME"];
        //            //strQryIns = strQryIns + "(REL_ID,NODE_ID," + DvCol[0]["FIELD_NAME"] + ",START_DATE,END_DATE) VALUES (";
        //            //strQryIns = strQryIns + (++intRelID) + ",";
        //            //strQryIns = strQryIns + NodeId + ",";
        //            //strQryIns = strQryIns + arrRelId[i] + ",";
        //            //strQryIns = strQryIns + "'" + DateTime.Now + "',";
        //            //strQryIns = strQryIns + "'" + EndDate + "')";
        //            //Int32 ret = sqlConn.SaveDataByFactory(strQryIns);

        //            strQryIns = strQryIns + " UPDATE " + ds.Tables[1].Rows[0]["TABLE_NAME"];
        //            strQryIns = strQryIns + " SET NODE_ID = " + NodeId;
        //            strQryIns = strQryIns + " WHERE " + DvCol[0]["FIELD_NAME"] + " = " + arrRelId[i];

        //            Util.WritInfoLog(ClsName, "AddRel", "Query for AddRel", string.Format("Query:{0}", strQryIns));
        //            Int32 ret = sqlConn.SaveDataByFactory(strQryIns);
        //            Util.WritInfoLog(ClsName, "AddRel", "End Query for AddRel");

        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        Util.WritErrorLog(ClsName, "AddRel", "AddRel", "", ex);
        //        throw ex;
        //    }
        //}

        public string GetAddRelGridData(Boolean bOnlydata)
        {
            try
            {

                Grid griddata = new Grid();
                griddata = GetjqgridCol(bOnlydata, "addrel");

                JavaScriptSerializer js = new JavaScriptSerializer();
                return js.Serialize(griddata);

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "GetAddRelGridData", "GetAddRelGridData", "", ex);
                throw ex;
            }
        }

        public string GetRelColAcRule(Boolean bOnlydata)
        {

            try
            {

                string primarykey = string.Empty;
                rulesData rulesData = new rulesData();

                Grid newgriddata = new Grid();
                Grid remgriddata = new Grid();

                Util.WritInfoLog(ClsName, "GetRelColAcRule()", "Start Creating Connection");
                Connection sqlConn = new Connection();
                Util.WritInfoLog(ClsName, "GetRelColAcRule()", "End Creating Connection");

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@HierarchyId", DbType.Int32, ParameterDirection.Input, 0, HierarchyId));
                lstparam.Add(new DBParameter("@dimname", DbType.String, ParameterDirection.Input, 0, dimname));

                Util.WritInfoLog(ClsName, "GetRelColAcRule", "method of usp_GetRelColAcRule", string.Format("method:{0}", "usp_GetjqgridCol"));
                DataSet ds = sqlConn.GetDataSetFromFactoryProc("usp_GetjqgridCol", ref(lstparam));
                Util.WritInfoLog(ClsName, "GetRelColAcRule", "End method of usp_GetRelColAcRule");

                DataTable dtCol = ds.Tables[1];
                DataView DvCol = new DataView(dtCol);
                DataView DvColPrimary = new DataView(dtCol);

                DvColPrimary.RowFilter = " IS_PRIMARY =TRUE";

                string strDim = string.Empty;
                string strColname = string.Empty;

                //get colname names of dimension
                foreach (DataRow dr in ds.Tables[1].Rows)
                {
                    if (strColname == "") { strColname = "DIM." + dr["FIELD_NAME"].ToString(); }
                    else { strColname = strColname + "," + "DIM." + dr["FIELD_NAME"].ToString(); }
                    // strColnameHist
                }


                int index = 0;
                List<colModel> listColmodel = new List<colModel>();
                List<string> columns = new List<string>();

                if (bOnlydata == false)
                {

                    //DvCol.RowFilter = " IS_PRIMARY =FALSE";

                    foreach (DataRowView dr in DvCol)
                    //foreach (DataRow dr in dtColnm.Rows)
                    {

                        //for columns
                        columns.Add(dr["DISPLAY"].ToString());
                        //for colmodel
                        colModel colmodel = new colModel();

                        colmodel.index = dr["FIELD_NAME"].ToString();
                        colmodel.name = dr["FIELD_NAME"].ToString();

                        Dictionary<string, object> edoptions = new Dictionary<string, object>();
                        Dictionary<string, object> editrules = new Dictionary<string, object>();
                        Dictionary<string, object> formatoptions = new Dictionary<string, object>();

                        if (dr["DATA_TYPE"].ToString().ToLower() == "number") //integer for whole number
                        {
                            colmodel.sorttype = "int";
                            colmodel.align = "right";
                            editrules.Add("number", true);
                            edoptions.Add("NullIfEmpty", true);

                            //formatter 
                            colmodel.formatter = "number";
                            //format option for number
                            formatoptions.Add("decimalSeparator", ".");
                            formatoptions.Add("defaulValue", 0);
                            formatoptions.Add("decimalPlaces", 2);
                        }
                        else if (dr["DATA_TYPE"].ToString().ToLower() == "varchar")
                        {
                            colmodel.sorttype = "string";
                            colmodel.align = "left";
                            //formatter                           
                            colmodel.formatter = "defaultFormat";
                        }
                        else if (dr["DATA_TYPE"].ToString().ToLower() == "date")
                        {
                            colmodel.sorttype = "date";
                            colmodel.align = "right";
                            editrules.Add("date", true);
                            colmodel.formatter = "date";
                            ////date format in mm/dd/yyyy
                            formatoptions.Add("srcformat", "ISO8601Long");
                            formatoptions.Add("newformat", "m/d/Y");
                        }
                        else if (dr["DATA_TYPE"].ToString().ToLower() == "float")
                        {
                            colmodel.sorttype = "float";
                            colmodel.align = "right";
                        }

                        //add key to col model
                        if (dr["IS_PRIMARY"].ToString().ToLower() == "true")
                        {
                            colmodel.key = true;
                        }

                        listColmodel.Add(colmodel);
                        index++;
                    }
                }

                // int index = 0;
                DvCol.RowFilter = " IS_PRIMARY =TRUE";
                primarykey = DvCol[0]["FIELD_NAME"].ToString();

                newgriddata.primarykey = primarykey;

                //remgriddata.datarow = remRows;
                remgriddata.primarykey = primarykey;
                newgriddata.colModelList = listColmodel;
                newgriddata.columns = columns;

                rulesData.newDimGrid = newgriddata;
                rulesData.remDimGrid = remgriddata;

                JavaScriptSerializer js = new JavaScriptSerializer();
                return js.Serialize(rulesData);

                //return rulesData;

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "GetRelDataAcRule", "GetRelDataAcRule", "", ex);
                throw ex;
            }
        }

        //before SP
        //get relation data depending on rule
        //public string GetRelColAcRule(Boolean bOnlydata)
        //{

        //    try
        //    {

        //        string primarykey = string.Empty;
        //        rulesData rulesData = new rulesData();

        //        Grid newgriddata = new Grid();
        //        Grid remgriddata = new Grid();

        //        Util.WritInfoLog(ClsName, "GetRelColAcRule()", "Start Creating Connection");
        //        Connection sqlConn = new Connection();
        //        Util.WritInfoLog(ClsName, "GetRelColAcRule()", "End Creating Connection");

        //        string strQry = " SELECT * FROM X_MASTER_LIST WHERE TABLE_ABBR= '" + dimname + "'";

        //        strQry = strQry + " SELECT * FROM X_MASTER_LIST_COLUMNS ";
        //        strQry = strQry + " WHERE LIST_ID= (SELECT LIST_ID FROM X_MASTER_LIST ";
        //        strQry = strQry + " WHERE TABLE_ABBR=  '" + dimname + "')";

        //        strQry = strQry + " SELECT X_HIER_MASTER_LIST_REL.TABLE_NAME FROM X_HIER_MASTER_LIST_REL ";
        //        strQry = strQry + " INNER JOIN X_MASTER_LIST ON ";
        //        strQry = strQry + " X_HIER_MASTER_LIST_REL.MASTER_LIST_ID =X_MASTER_LIST.LIST_ID ";
        //        strQry = strQry + " WHERE HIERARCHY_ID= " + HierarchyId;
        //        strQry = strQry + " AND TABLE_ABBR= '" + dimname + "'";


        //        Util.WritInfoLog(ClsName, "GetRelColAcRule", "Query for GetRelColAcRule", string.Format("Query:{0}", strQry));               
        //        DataSet ds = sqlConn.GetDataSetFromFactory(strQry);
        //        Util.WritInfoLog(ClsName, "GetRelColAcRule", "End Query GetRelColAcRule");

        //        DataTable dtCol = ds.Tables[1];
        //        DataView DvCol = new DataView(dtCol);
        //        DataView DvColPrimary = new DataView(dtCol);

        //        DvColPrimary.RowFilter = " IS_PRIMARY =TRUE";

        //        string strDim = string.Empty;
        //        string strColname = string.Empty;

        //        //get colname names of dimension
        //        foreach (DataRow dr in ds.Tables[1].Rows)
        //        {
        //            if (strColname == "") { strColname = "DIM." + dr["FIELD_NAME"].ToString(); }
        //            else { strColname = strColname + "," + "DIM." + dr["FIELD_NAME"].ToString(); }
        //            // strColnameHist
        //        }


        //        int index = 0;
        //        List<colModel> listColmodel = new List<colModel>();
        //        List<string> columns = new List<string>();

        //        if (bOnlydata == false)
        //        {

        //            //DvCol.RowFilter = " IS_PRIMARY =FALSE";

        //            foreach (DataRowView dr in DvCol)
        //            //foreach (DataRow dr in dtColnm.Rows)
        //            {

        //                //for columns
        //                columns.Add(dr["DISPLAY"].ToString());
        //                //for colmodel
        //                colModel colmodel = new colModel();

        //                colmodel.index = dr["FIELD_NAME"].ToString();
        //                colmodel.name = dr["FIELD_NAME"].ToString();

        //                Dictionary<string, object> edoptions = new Dictionary<string, object>();
        //                Dictionary<string, object> editrules = new Dictionary<string, object>();
        //                Dictionary<string, object> formatoptions = new Dictionary<string, object>();

        //                if (dr["DATA_TYPE"].ToString().ToLower() == "number") //integer for whole number
        //                {
        //                    colmodel.sorttype = "int";
        //                    colmodel.align = "right";
        //                    editrules.Add("number", true);
        //                    edoptions.Add("NullIfEmpty", true);

        //                    //formatter 
        //                    colmodel.formatter = "number";
        //                    //format option for number
        //                    formatoptions.Add("decimalSeparator", ".");
        //                    formatoptions.Add("defaulValue", 0);
        //                    formatoptions.Add("decimalPlaces", 2);
        //                }
        //                else if (dr["DATA_TYPE"].ToString().ToLower() == "varchar")
        //                {
        //                    colmodel.sorttype = "string";
        //                    colmodel.align = "left";
        //                    //formatter                           
        //                    colmodel.formatter = "defaultFormat";
        //                }
        //                else if (dr["DATA_TYPE"].ToString().ToLower() == "date")
        //                {
        //                    colmodel.sorttype = "date";
        //                    colmodel.align = "right";
        //                    editrules.Add("date", true);
        //                    colmodel.formatter = "date";
        //                    ////date format in mm/dd/yyyy
        //                    formatoptions.Add("srcformat", "ISO8601Long");
        //                    formatoptions.Add("newformat", "m/d/Y");
        //                }
        //                else if (dr["DATA_TYPE"].ToString().ToLower() == "float")
        //                {
        //                    colmodel.sorttype = "float";
        //                    colmodel.align = "right";
        //                }

        //                //add key to col model
        //                if (dr["IS_PRIMARY"].ToString().ToLower() == "true")
        //                {
        //                    colmodel.key = true;
        //                }

        //                listColmodel.Add(colmodel);
        //                index++;
        //            }
        //        }

        //        // int index = 0;
        //        DvCol.RowFilter = " IS_PRIMARY =TRUE";
        //        primarykey = DvCol[0]["FIELD_NAME"].ToString();

        //        newgriddata.primarykey = primarykey;

        //        //remgriddata.datarow = remRows;
        //        remgriddata.primarykey = primarykey;
        //        newgriddata.colModelList = listColmodel;
        //        newgriddata.columns = columns;

        //        rulesData.newDimGrid = newgriddata;
        //        rulesData.remDimGrid = remgriddata;

        //        JavaScriptSerializer js = new JavaScriptSerializer();
        //        return js.Serialize(rulesData);

        //        //return rulesData;

        //    }
        //    catch (Exception ex)
        //    {
        //        Util.WritErrorLog(ClsName, "GetRelDataAcRule", "GetRelDataAcRule", "", ex);
        //        throw ex;
        //    }
        //}

        //get new relation data depending on rule
        public string GetRelDataNewAcRule(Boolean bOnlydata)
        {
            try
            {

                Util.WritInfoLog(ClsName, "GetRelDataNewAcRule()", "Start Creating Connection");
                Connection sqlConn = new Connection();
                Util.WritInfoLog(ClsName, "GetRelDataNewAcRule()", "End Creating Connection");

                List<DBParameter> lstparam = new List<DBParameter>();

                int start = ((page - 1) * rows);

                if (sidx == "" || sidx == null)
                {
                    sidx = "DIM.DIM_ID" ;
                }

                string sortExp = string.Empty;
                if (sidx != "" && sidx != null)
                {
                    sortExp = "DIM." + sidx + " " + sord;
                }             

                lstparam.Add(new DBParameter("@TABLE_ABBR", DbType.String, ParameterDirection.Input, 255, dimname));
                lstparam.Add(new DBParameter("@HierarchyId", DbType.Int32, ParameterDirection.Input, 0, HierarchyId));
                lstparam.Add(new DBParameter("@TableName", DbType.String, ParameterDirection.Input, 255, TableName));

                lstparam.Add(new DBParameter("@NodeId", DbType.Int32, ParameterDirection.Input, 0, NodeId));
                lstparam.Add(new DBParameter("@start", DbType.Int32, ParameterDirection.Input, 0, start));
                lstparam.Add(new DBParameter("@rows", DbType.Int32, ParameterDirection.Input, 0, rows));
               
                lstparam.Add(new DBParameter("@query", DbType.String, ParameterDirection.Input, 1000, query.Replace("\"", "'")));
                lstparam.Add(new DBParameter("@sortExp", DbType.String, ParameterDirection.Input, 1000, sortExp));
                lstparam.Add(new DBParameter("@_search", DbType.Boolean, ParameterDirection.Input, 0, _search));

                lstparam.Add(new DBParameter("@queryRel", DbType.String, ParameterDirection.Input, 1000, queryRel));

                Util.WritInfoLog(ClsName, "GetRelDataNewAcRule", "method for GetRelDataNewAcRule");
                DataSet dsData = sqlConn.GetDataSetFromFactoryProc("usp_GetRelDataNewAcRule", ref(lstparam));
                Util.WritInfoLog(ClsName, "GetRelDataNewAcRule", "End method for GetRelDataNewAcRule");

                DataTable dtCol = dsData.Tables[0];
                DataView DvCol = new DataView(dtCol);
                DataView DvColPrimary = new DataView(dtCol);

                DataTable dtnew = dsData.Tables[1];
                DataTable dtColnm = dsData.Tables[3];

                dtnew.TableName = "rule";
                dtColnm = DvCol.ToTable();

                int cnt = 0;
                cnt = Convert.ToInt32(dsData.Tables[2].Rows[0]["CNT"]);
                //cnt = Convert.ToInt32(dsData.Tables[1].Rows.Count);
                string strData = Common.JsonForJqgrid(dtnew, dtColnm, rows, cnt, page);
                return strData;

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "GetRelDataAcRule", "GetRelDataAcRule", "", ex);
                throw ex;
            }
        }

        //before SP
        ////get new relation data depending on rule
        //public string GetRelDataNewAcRule(Boolean bOnlydata)
        //{
        //    try
        //    {

        //        //string primarykey = string.Empty;
        //        //rulesData rulesData = new rulesData();

        //        //Grid newgriddata = new Grid();
        //        //Grid remgriddata = new Grid();

        //        Util.WritInfoLog(ClsName, "GetRelDataNewAcRule()", "Start Creating Connection");
        //        Connection sqlConn = new Connection();
        //        Util.WritInfoLog(ClsName, "GetRelDataNewAcRule()", "End Creating Connection");

        //        string strQry = " SELECT * FROM X_MASTER_LIST WHERE TABLE_ABBR= '" + dimname + "'";

        //        strQry = strQry + " SELECT * FROM X_MASTER_LIST_COLUMNS ";
        //        strQry = strQry + " WHERE LIST_ID= (SELECT LIST_ID FROM X_MASTER_LIST ";
        //        strQry = strQry + " WHERE TABLE_ABBR=  '" + dimname + "')";

        //        strQry = strQry + " SELECT X_HIER_MASTER_LIST_REL.TABLE_NAME FROM X_HIER_MASTER_LIST_REL ";
        //        strQry = strQry + " INNER JOIN X_MASTER_LIST ON ";
        //        strQry = strQry + " X_HIER_MASTER_LIST_REL.MASTER_LIST_ID =X_MASTER_LIST.LIST_ID ";
        //        strQry = strQry + " WHERE HIERARCHY_ID= " + HierarchyId;
        //        strQry = strQry + " AND TABLE_ABBR= '" + dimname + "'";


        //        Util.WritInfoLog(ClsName, "GetRelDataNewAcRule", "Query for getting details in GetRelDataNewAcRule", string.Format("Query:{0}", strQry));
        //        DataSet ds = sqlConn.GetDataSetFromFactory(strQry);
        //        Util.WritInfoLog(ClsName, "GetRelDataNewAcRule", "End Query for getting details in GetRelDataNewAcRule");

        //        DataTable dtCol = ds.Tables[1];
        //        DataView DvCol = new DataView(dtCol);
        //        DataView DvColPrimary = new DataView(dtCol);

        //        DvColPrimary.RowFilter = " IS_PRIMARY =TRUE";

        //        string strDim = string.Empty;
        //        string strColname = string.Empty;

        //        //get colname names of dimension
        //        foreach (DataRow dr in ds.Tables[1].Rows)
        //        {
        //            if (strColname == "") { strColname = "DIM." + dr["FIELD_NAME"].ToString(); }
        //            else { strColname = strColname + "," + "DIM." + dr["FIELD_NAME"].ToString(); }
        //        }

        //        //original logic before url jqgrid
        //        //// NEW LOGIC FOR new dimension
        //        //strDim = " WITH Hierarchy(Node_id,Node_name, Parent_ID, HLevel) AS ( ";
        //        //strDim = strDim + " SELECT Node_id, [Node_name], Parent_ID , 0 AS HLevel FROM " + TableName;
        //        //strDim = strDim + " WHERE NODE_ID = ( SELECT Parent_ID FROM " + TableName + " WHERE NODE_ID = " + NodeId + " ) ";
        //        //strDim = strDim + " UNION ALL ";
        //        //strDim = strDim + " SELECT  parent.Node_id, parent.[Node_name], parent.Parent_ID , HLevel + 1 ";
        //        //strDim = strDim + " FROM " + TableName + " parent ";
        //        //strDim = strDim + " INNER JOIN Hierarchy ON parent.Node_id = Hierarchy.Parent_ID ) ";

        //        //strDim = strDim + " SELECT DIM.* FROM Hierarchy HER ";
        //        //strDim = strDim + " INNER JOIN " + ds.Tables[2].Rows[0]["TABLE_NAME"] + " REL ON REL.NODE_ID = HER.Node_id ";
        //        //strDim = strDim + " INNER JOIN " + ds.Tables[0].Rows[0]["TABLE_NAME"] + " DIM ON DIM." + DvCol[0]["FIELD_NAME"] + "= REL." + DvCol[0]["FIELD_NAME"];
        //        //strDim = strDim + " WHERE " + query;
        //        //strDim = strDim + " ORDER BY " + DvCol[0]["FIELD_NAME"];               

        //        if (sidx == "" || sidx == null)
        //        {
        //            sidx = "DIM." + Convert.ToString(DvCol[0]["FIELD_NAME"]);
        //        }

        //        string sortExp = string.Empty;//= " HLevel DESC ";
        //        if (sidx != "" && sidx != null)
        //        {
        //            sortExp = "DIM." + sidx + " " + sord;
        //        }

        //        int start = ((page - 1) * rows);

        //        // NEW LOGIC FOR new dimension
        //        strDim = " WITH Hierarchy(Node_id,Node_name, Parent_ID, HLevel) AS ( ";
        //        strDim = strDim + " SELECT Node_id, [Node_name], Parent_ID , 0 AS HLevel FROM " + TableName;
        //        strDim = strDim + " WHERE NODE_ID = ( SELECT Parent_ID FROM " + TableName + " WHERE NODE_ID = " + NodeId + " ) ";
        //        strDim = strDim + " UNION ALL ";
        //        strDim = strDim + " SELECT  parent.Node_id, parent.[Node_name], parent.Parent_ID , HLevel + 1 ";
        //        strDim = strDim + " FROM " + TableName + " parent ";
        //        strDim = strDim + " INNER JOIN Hierarchy ON parent.Node_id = Hierarchy.Parent_ID ) ";
        //        strDim = strDim + " SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY " + sortExp + ") AS Row, ";
        //        strDim = strDim + " DIM.* FROM Hierarchy HER ";
        //        strDim = strDim + " INNER JOIN " + ds.Tables[2].Rows[0]["TABLE_NAME"] + " REL ON REL.NODE_ID = HER.Node_id ";
        //        strDim = strDim + " INNER JOIN " + ds.Tables[0].Rows[0]["TABLE_NAME"] + " DIM ON DIM." + DvColPrimary[0]["FIELD_NAME"];
        //        strDim = strDim + "= REL." + DvColPrimary[0]["FIELD_NAME"];
        //        if (queryRel != "")
        //        {
        //            strDim = strDim + " WHERE " + queryRel;
        //        }

        //        //for search filter
        //        if (_search && (query != null || query != ""))
        //        {
        //            strDim = strDim + " AND " + query.Replace("\"", "'");//errString.Replace("docment", "document");
        //        }
        //        strDim = strDim + ")tmp";
        //        strDim = strDim + " WHERE Row > " + start + " AND Row <= " + (start + rows) + ";";
        //        //strDim = strDim + " ORDER BY " + DvCol[0]["FIELD_NAME"];

        //        //cnt
        //        strDim = strDim + " WITH Hierarchy(Node_id,Node_name, Parent_ID, HLevel) AS ( ";
        //        strDim = strDim + " SELECT Node_id, [Node_name], Parent_ID , 0 AS HLevel FROM " + TableName;
        //        strDim = strDim + " WHERE NODE_ID = ( SELECT Parent_ID FROM " + TableName + " WHERE NODE_ID = " + NodeId + " ) ";
        //        strDim = strDim + " UNION ALL ";
        //        strDim = strDim + " SELECT  parent.Node_id, parent.[Node_name], parent.Parent_ID , HLevel + 1 ";
        //        strDim = strDim + " FROM " + TableName + " parent ";
        //        strDim = strDim + " INNER JOIN Hierarchy ON parent.Node_id = Hierarchy.Parent_ID ) ";
        //        strDim = strDim + " SELECT COUNT(*) CNT FROM (SELECT ROW_NUMBER() OVER (ORDER BY " + sortExp + ") AS Row, ";
        //        strDim = strDim + " DIM.* FROM Hierarchy HER ";
        //        strDim = strDim + " INNER JOIN " + ds.Tables[2].Rows[0]["TABLE_NAME"] + " REL ON REL.NODE_ID = HER.Node_id ";
        //        strDim = strDim + " INNER JOIN " + ds.Tables[0].Rows[0]["TABLE_NAME"] + " DIM ON DIM." + DvColPrimary[0]["FIELD_NAME"];
        //        strDim = strDim + "= REL." + DvColPrimary[0]["FIELD_NAME"];
        //        if (queryRel != "")
        //        {
        //            strDim = strDim + " WHERE " + queryRel;
        //        }

        //        //for search filter
        //        if (_search && (query != null || query != ""))
        //        {
        //            strDim = strDim + " AND " + query.Replace("\"", "'");//errString.Replace("docment", "document");
        //        }
        //        strDim = strDim + ")tmp ;";


        //        //get column names of table
        //        strDim = strDim + " SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('" + ds.Tables[0].Rows[0]["TABLE_NAME"];
        //        strDim = strDim + " ')";

        //        Util.WritInfoLog(ClsName, "GetRelDataNewAcRule", "Query for  get new relation data depending on rule", string.Format("Query:{0}", strDim));
        //        DataSet dsData = sqlConn.GetDataSetFromFactory(strDim);
        //        Util.WritInfoLog(ClsName, "GetRelDataNewAcRule", "End Query  get new relation data depending on rule");

        //        DataTable dtnew = dsData.Tables[0];
        //        // DataTable dtrem = dsData.Tables[0];
        //        DataTable dtColnm = dsData.Tables[2];

        //        dtnew.TableName = "rule";
        //        //DvCol.RowFilter = " IS_PRIMARY =FALSE";
        //        //dtColnm.re
        //        dtColnm = DvCol.ToTable();

        //        int cnt = 0;
        //        cnt = Convert.ToInt32(dsData.Tables[1].Rows[0]["CNT"]);
        //        string strData = Common.JsonForJqgrid(dtnew, dtColnm, rows, cnt, page);
        //        return strData;


        //    }
        //    catch (Exception ex)
        //    {
        //        Util.WritErrorLog(ClsName, "GetRelDataAcRule", "GetRelDataAcRule", "", ex);
        //        throw ex;
        //    }
        //}

        //get relation data to remove depending on rule
        public string GetRelDataRemAcRule(Boolean bOnlydata)
        {
            try
            {

                Util.WritInfoLog(ClsName, "GetRelDataRemAcRule()", "Start Creating Connection");
                Connection sqlConn = new Connection();
                Util.WritInfoLog(ClsName, "GetRelDataRemAcRule()", "End Creating Connection");

                List<DBParameter> lstparam = new List<DBParameter>();

                int start = ((page - 1) * rows);

                if (sidx == "" || sidx == null)
                {
                    sidx = "DIM.DIM_ID";
                }

                string sortExp = string.Empty;
                if (sidx != "" && sidx != null)
                {
                    sortExp = sidx + " " + sord;
                }

                lstparam.Add(new DBParameter("@TABLE_ABBR", DbType.String, ParameterDirection.Input, 255, dimname));
                lstparam.Add(new DBParameter("@HierarchyId", DbType.Int32, ParameterDirection.Input, 0, HierarchyId));
                lstparam.Add(new DBParameter("@TableName", DbType.String, ParameterDirection.Input, 255, TableName));

                lstparam.Add(new DBParameter("@NodeId", DbType.Int32, ParameterDirection.Input, 0, NodeId));
                lstparam.Add(new DBParameter("@start", DbType.Int32, ParameterDirection.Input, 0, start));
                lstparam.Add(new DBParameter("@rows", DbType.Int32, ParameterDirection.Input, 0, rows));

                lstparam.Add(new DBParameter("@query", DbType.String, ParameterDirection.Input, 1000, query.Replace("\"", "'")));
                lstparam.Add(new DBParameter("@sortExp", DbType.String, ParameterDirection.Input, 1000, sortExp));
                lstparam.Add(new DBParameter("@_search", DbType.Boolean, ParameterDirection.Input, 0, _search));

                lstparam.Add(new DBParameter("@queryRel", DbType.String, ParameterDirection.Input, 1000, queryRel));

                Util.WritInfoLog(ClsName, "GetRelDataRemAcRule", "method for GetRelDataRemAcRule");
                DataSet dsData = sqlConn.GetDataSetFromFactoryProc("usp_GetRelDataRemAcRule", ref(lstparam));
                Util.WritInfoLog(ClsName, "GetRelDataRemAcRule", "End method for GetRelDataRemAcRule");

                DataTable dtCol = dsData.Tables[0];
                DataView DvCol = new DataView(dtCol);
                DataView DvColPrimary = new DataView(dtCol);

                DataTable dtrem = dsData.Tables[1];
                DataTable dtColnm = dsData.Tables[3];

                dtrem.TableName = "rule";
                dtColnm = DvCol.ToTable();

                int cnt = 0;
                cnt = Convert.ToInt32(dsData.Tables[2].Rows[0]["CNT"]);
                //cnt = Convert.ToInt32(dsData.Tables[1].Rows.Count);
                string strData = Common.JsonForJqgrid(dtrem, dtColnm, rows, cnt, page);
                return strData;

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "GetRelDataRemAcRule", "GetRelDataRemAcRule", "", ex);
                throw ex;
            }
        }

        //original before SP
        //get remove relation data a/c to rule      
        //public string GetRelDataRemAcRule(Boolean bOnlydata)
        //{
        //    try
        //    {

        //        Util.WritInfoLog(ClsName, "GetRelDataRemAcRule()", "Start Creating Connection");
        //        Connection sqlConn = new Connection();
        //        Util.WritInfoLog(ClsName, "GetRelDataRemAcRule()", "End Creating Connection");

        //        string strQry = " SELECT * FROM X_MASTER_LIST WHERE TABLE_ABBR= '" + dimname + "'";

        //        strQry = strQry + " SELECT * FROM X_MASTER_LIST_COLUMNS ";
        //        strQry = strQry + " WHERE LIST_ID= (SELECT LIST_ID FROM X_MASTER_LIST ";
        //        strQry = strQry + " WHERE TABLE_ABBR=  '" + dimname + "')";

        //        strQry = strQry + " SELECT X_HIER_MASTER_LIST_REL.TABLE_NAME FROM X_HIER_MASTER_LIST_REL ";
        //        strQry = strQry + " INNER JOIN X_MASTER_LIST ON ";
        //        strQry = strQry + " X_HIER_MASTER_LIST_REL.MASTER_LIST_ID =X_MASTER_LIST.LIST_ID ";
        //        strQry = strQry + " WHERE HIERARCHY_ID= " + HierarchyId;
        //        strQry = strQry + " AND TABLE_ABBR= '" + dimname + "'";

        //        Util.WritInfoLog(ClsName, "GetRelDataRemAcRule", "Query for get details in GetRelDataRemAcRule", string.Format("Query:{0}", strQry));
        //        DataSet ds = sqlConn.GetDataSetFromFactory(strQry);
        //        Util.WritInfoLog(ClsName, "GetRelDataRemAcRule", "End Query for get details in GetRelDataRemAcRule");

        //        DataTable dtCol = ds.Tables[1];
        //        DataView DvCol = new DataView(dtCol);
        //        DataView DvColPrimary = new DataView(dtCol);

        //        DvColPrimary.RowFilter = " IS_PRIMARY =TRUE";

        //        string strDim = string.Empty;
        //        string strColname = string.Empty;

        //        //get colname names of dimension
        //        foreach (DataRow dr in ds.Tables[1].Rows)
        //        {
        //            if (strColname == "") { strColname = "DIM." + dr["FIELD_NAME"].ToString(); }
        //            else { strColname = strColname + "," + "DIM." + dr["FIELD_NAME"].ToString(); }
        //        }

        //        if (sidx == "" || sidx == null)
        //        {
        //            //sidx = "DIM." + Convert.ToString(DvCol[0]["FIELD_NAME"]);
        //            sidx = Convert.ToString(DvCol[0]["FIELD_NAME"]);
        //        }

        //        string sortExp = string.Empty;//= " HLevel DESC ";
        //        if (sidx != "" && sidx != null)
        //        {
        //            //sortExp = "DIM." + sidx + " " + sord;
        //            sortExp = sidx + " " + sord;
        //        }

        //        int start = ((page - 1) * rows);

        //        ////remove dimension
        //        //strDim = "SELECT * FROM (";
        //        //strDim = strDim + " SELECT " + " ROW_NUMBER() OVER (ORDER BY " + sortExp + ") AS Row, ";
        //        //strDim = strDim +  strColname + "  FROM " + ds.Tables[0].Rows[0]["TABLE_NAME"] + " DIM ";
        //        //strDim = strDim + " INNER JOIN " + ds.Tables[2].Rows[0]["TABLE_NAME"];
        //        //strDim = strDim + " ON DIM." + DvCol[0]["FIELD_NAME"];
        //        //strDim = strDim + " = " + ds.Tables[2].Rows[0]["TABLE_NAME"] + "." + DvCol[0]["FIELD_NAME"];
        //        //strDim = strDim + " WHERE NODE_ID= " + NodeId;
        //        //strDim = strDim + " EXCEPT ";
        //        //strDim = strDim + " SELECT " + " ROW_NUMBER() OVER (ORDER BY " + sortExp + " ) AS Row,";
        //        //strDim = strDim + strColname + "  FROM " + ds.Tables[0].Rows[0]["TABLE_NAME"] + " DIM ";
        //        //strDim = strDim + " WHERE " + query + ")tmp";
        //        //strDim = strDim + " WHERE Row > " + start + " AND Row <= " + (start + rows) + ";";

        //        //remove dimension
        //        strDim = "SELECT * FROM (";
        //        strDim = strDim + "SELECT ROW_NUMBER() OVER (ORDER BY " + sortExp + ") AS Row,* FROM (";
        //        strDim = strDim + " SELECT ";
        //        strDim = strDim + strColname + "  FROM " + ds.Tables[0].Rows[0]["TABLE_NAME"] + " DIM ";
        //        strDim = strDim + " INNER JOIN " + ds.Tables[2].Rows[0]["TABLE_NAME"];
        //        strDim = strDim + " ON DIM." + DvColPrimary[0]["FIELD_NAME"];
        //        strDim = strDim + " = " + ds.Tables[2].Rows[0]["TABLE_NAME"] + "." + DvColPrimary[0]["FIELD_NAME"];
        //        strDim = strDim + " WHERE NODE_ID= " + NodeId;
        //        ////for search filter
        //        if (_search && (query != null || query != ""))
        //        {
        //            strDim = strDim + " AND " + query.Replace("\"", "'");//errString.Replace("docment", "document");
        //        }

        //        strDim = strDim + " EXCEPT ";
        //        strDim = strDim + " SELECT ";
        //        strDim = strDim + strColname + "  FROM " + ds.Tables[0].Rows[0]["TABLE_NAME"] + " DIM ";
        //        if (queryRel != "")
        //        {
        //            strDim = strDim + " WHERE " + queryRel;
        //        }
        //        strDim = strDim + ")tmp";
        //        strDim = strDim + " ) temp ";
        //        strDim = strDim + " WHERE temp.Row > " + start + " AND temp.Row <= " + (start + rows) + ";";

        //        //cnt
        //        //remove dimension
        //        strDim = strDim + "SELECT COUNT(*) CNT FROM (";
        //        strDim = strDim + "SELECT ROW_NUMBER() OVER (ORDER BY " + sortExp + ") AS Row,* FROM (";
        //        strDim = strDim + " SELECT ";
        //        strDim = strDim + strColname + "  FROM " + ds.Tables[0].Rows[0]["TABLE_NAME"] + " DIM ";
        //        strDim = strDim + " INNER JOIN " + ds.Tables[2].Rows[0]["TABLE_NAME"];
        //        strDim = strDim + " ON DIM." + DvColPrimary[0]["FIELD_NAME"];
        //        strDim = strDim + " = " + ds.Tables[2].Rows[0]["TABLE_NAME"] + "." + DvColPrimary[0]["FIELD_NAME"];
        //        strDim = strDim + " WHERE NODE_ID= " + NodeId;
        //        ////for search filter
        //        if (_search && (query != null || query != ""))
        //        {
        //            strDim = strDim + " AND " + query.Replace("\"", "'");//errString.Replace("docment", "document");
        //        }

        //        strDim = strDim + " EXCEPT ";
        //        strDim = strDim + " SELECT ";
        //        strDim = strDim + strColname + "  FROM " + ds.Tables[0].Rows[0]["TABLE_NAME"] + " DIM ";
        //        if (queryRel != "")
        //        {
        //            strDim = strDim + " WHERE " + queryRel;
        //        }
        //        strDim = strDim + ")tmp";
        //        strDim = strDim + " ) temp ;";

        //        ///
        //        //strDim = strDim + " SELECT COUNT(*) CNT FROM (";
        //        //strDim = strDim + " SELECT " + " ROW_NUMBER() OVER (ORDER BY " + sortExp + ") AS Row, ";
        //        //strDim = strDim + strColname + "  FROM " + ds.Tables[0].Rows[0]["TABLE_NAME"] + " DIM ";
        //        //strDim = strDim + " INNER JOIN " + ds.Tables[2].Rows[0]["TABLE_NAME"];
        //        //strDim = strDim + " ON DIM." + DvColPrimary[0]["FIELD_NAME"];
        //        //strDim = strDim + " = " + ds.Tables[2].Rows[0]["TABLE_NAME"] + "." + DvColPrimary[0]["FIELD_NAME"];
        //        //strDim = strDim + " WHERE NODE_ID= " + NodeId;
        //        //////for search filter
        //        //if (_search && (query != null || query != ""))
        //        //{
        //        //    strDim = strDim + " AND " + query.Replace("\"", "'");//errString.Replace("docment", "document");
        //        //}  

        //        //strDim = strDim + " EXCEPT ";
        //        ////strDim = strDim + " SELECT " + " ROW_NUMBER() OVER (ORDER BY " + sortExp + " ) AS Row,";
        //        ////strDim = strDim + strColname + "  FROM " + ds.Tables[0].Rows[0]["TABLE_NAME"] + " DIM ";
        //        ////if (queryRel != "")
        //        ////{
        //        ////    strDim = strDim + " WHERE " + queryRel ;
        //        ////}

        //        ////strDim = strDim + ")tmp";
        //        //strDim = strDim + " SELECT ";
        //        //strDim = strDim + strColname + "  FROM " + ds.Tables[0].Rows[0]["TABLE_NAME"] + " DIM ";
        //        //if (queryRel != "")
        //        //{
        //        //    strDim = strDim + " WHERE " + queryRel;
        //        //}
        //        //strDim = strDim + ")tmp";
        //        //strDim = strDim + " ) temp ;";

        //        //get column names of table
        //        strDim = strDim + " SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('" + ds.Tables[0].Rows[0]["TABLE_NAME"];
        //        strDim = strDim + " ')";


        //        Util.WritInfoLog(ClsName, "GetRelDataRemAcRule", "Query for GetRelDataRemAcRule", string.Format("Query:{0}", strDim));
        //        DataSet dsData = sqlConn.GetDataSetFromFactory(strDim);
        //        Util.WritInfoLog(ClsName, "GetRelDataRemAcRule", "End Query for GetRelDataRemAcRule");

        //        // DataTable dtnew = dsData.Tables[0];
        //        DataTable dtrem = dsData.Tables[0];
        //        DataTable dtColnm = dsData.Tables[2];

        //        dtrem.TableName = "rule";
        //        //DvCol.RowFilter = " IS_PRIMARY =FALSE";
        //        //dtColnm.re
        //        dtColnm = DvCol.ToTable();

        //        int cnt = 0;
        //        cnt = Convert.ToInt32(dsData.Tables[1].Rows[0]["CNT"]);
        //        string strData = Common.JsonForJqgrid(dtrem, dtColnm, rows, cnt, page);
        //        return strData;

        //    }
        //    catch (Exception ex)
        //    {
        //        Util.WritErrorLog(ClsName, "GetRelDataAcRule", "GetRelDataAcRule", "", ex);
        //        throw ex;
        //    }
        //}

        //original before url jqgrid
        ////get relation data depending on rule
        //public string GetRelDataAcRule(Boolean bOnlydata)
        //{

        //    try
        //    {

        //        string primarykey = string.Empty;
        //        rulesData rulesData = new rulesData();

        //        Grid newgriddata = new Grid();
        //        Grid remgriddata = new Grid();

        //        Connection sqlConn = new Connection();
        //        string strQry = " SELECT * FROM X_MASTER_LIST WHERE TABLE_ABBR= '" + dimname + "'";

        //        strQry = strQry + " SELECT * FROM X_MASTER_LIST_COLUMNS ";
        //        strQry = strQry + " WHERE LIST_ID= (SELECT LIST_ID FROM X_MASTER_LIST ";
        //        strQry = strQry + " WHERE TABLE_ABBR=  '" + dimname + "')";

        //        strQry = strQry + " SELECT X_HIER_MASTER_LIST_REL.TABLE_NAME FROM X_HIER_MASTER_LIST_REL ";
        //        strQry = strQry + " INNER JOIN X_MASTER_LIST ON ";
        //        strQry = strQry + " X_HIER_MASTER_LIST_REL.MASTER_LIST_ID =X_MASTER_LIST.LIST_ID ";
        //        strQry = strQry + " WHERE HIERARCHY_ID= " + HierarchyId;
        //        strQry = strQry + " AND TABLE_ABBR= '" + dimname + "'";

        //        //strQry = strQry + " SELECT X_HIER_MASTER_LIST_RULES.TABLE_NAME FROM X_HIER_MASTER_LIST_RULES ";
        //        //strQry = strQry + " INNER JOIN X_MASTER_LIST ON ";
        //        //strQry = strQry + " X_HIER_MASTER_LIST_RULES.MASTER_LIST_ID =X_MASTER_LIST.LIST_ID ";
        //        //strQry = strQry + " WHERE HIERARCHY_ID= " + HierarchyId;
        //        //strQry = strQry + " AND TABLE_ABBR= '" + dimname + "'";

        //        DataSet ds = sqlConn.GetDataSetFromFactory(strQry);
        //        DataTable dtCol = ds.Tables[1];
        //        DataView DvCol = new DataView(dtCol);
        //        DataView DvColPrimary = new DataView(dtCol);

        //        DvColPrimary.RowFilter = " IS_PRIMARY =TRUE";

        //        string strDim = string.Empty;
        //        string strColname = string.Empty;

        //        //get colname names of dimension
        //        foreach (DataRow dr in ds.Tables[1].Rows)
        //        {
        //            if (strColname == "") { strColname = "DIM." + dr["FIELD_NAME"].ToString(); }
        //            else { strColname = strColname + "," + "DIM." + dr["FIELD_NAME"].ToString(); }
        //            // strColnameHist
        //        }

        //        ////new dimension
        //        //strDim = " SELECT " + strColname + "  FROM " + ds.Tables[0].Rows[0]["TABLE_NAME"] + " DIM ";
        //        //strDim = strDim + " WHERE " + query;

        //        //strDim = strDim + " EXCEPT ";

        //        //strDim = strDim + " SELECT " + strColname + "  FROM " + ds.Tables[0].Rows[0]["TABLE_NAME"] + " DIM ";
        //        //strDim = strDim + " INNER JOIN " + ds.Tables[2].Rows[0]["TABLE_NAME"];
        //        //strDim = strDim + " ON DIM." + DvCol[0]["FIELD_NAME"];
        //        //strDim = strDim + " = " + ds.Tables[2].Rows[0]["TABLE_NAME"] + "." + DvCol[0]["FIELD_NAME"];
        //        //strDim = strDim + " WHERE NODE_ID= " + NodeId;
        //        //strDim = strDim + " ORDER BY " + DvCol[0]["FIELD_NAME"];


        //        // NEW LOGIC FOR new dimension
        //        strDim = " WITH Hierarchy(Node_id,Node_name, Parent_ID, HLevel) AS ( ";
        //        strDim = strDim + " SELECT Node_id, [Node_name], Parent_ID , 0 AS HLevel FROM " + TableName;
        //        strDim = strDim + " WHERE NODE_ID = ( SELECT Parent_ID FROM " + TableName + " WHERE NODE_ID = " + NodeId + " ) ";
        //        strDim = strDim + " UNION ALL ";
        //        strDim = strDim + " SELECT  parent.Node_id, parent.[Node_name], parent.Parent_ID , HLevel + 1 ";
        //        strDim = strDim + " FROM " + TableName + " parent ";
        //        strDim = strDim + " INNER JOIN Hierarchy ON parent.Node_id = Hierarchy.Parent_ID ) ";

        //        //strDim = strDim + " SELECT " + strCols + " FROM Hierarchy HER ";
        //        strDim = strDim + " SELECT DIM.* FROM Hierarchy HER ";
        //        strDim = strDim + " INNER JOIN " + ds.Tables[2].Rows[0]["TABLE_NAME"] + " REL ON REL.NODE_ID = HER.Node_id ";
        //        strDim = strDim + " INNER JOIN " + ds.Tables[0].Rows[0]["TABLE_NAME"] + " DIM ON DIM." + DvCol[0]["FIELD_NAME"] + "= REL." + DvCol[0]["FIELD_NAME"];
        //        strDim = strDim + " WHERE " + query;
        //        //strDim = strDim + " ORDER BY HLevel DESC ";
        //        strDim = strDim + " ORDER BY " + DvCol[0]["FIELD_NAME"];


        //        //remove dimension
        //        strDim = strDim + " SELECT " + strColname + "  FROM " + ds.Tables[0].Rows[0]["TABLE_NAME"] + " DIM ";
        //        strDim = strDim + " INNER JOIN " + ds.Tables[2].Rows[0]["TABLE_NAME"];
        //        strDim = strDim + " ON DIM." + DvCol[0]["FIELD_NAME"];
        //        strDim = strDim + " = " + ds.Tables[2].Rows[0]["TABLE_NAME"] + "." + DvCol[0]["FIELD_NAME"];
        //        strDim = strDim + " WHERE NODE_ID= " + NodeId;

        //        strDim = strDim + " EXCEPT ";

        //        strDim = strDim + " SELECT " + strColname + "  FROM " + ds.Tables[0].Rows[0]["TABLE_NAME"] + " DIM ";
        //        strDim = strDim + " WHERE " + query;
        //        strDim = strDim + " ORDER BY " + DvCol[0]["FIELD_NAME"];

        //        //get column names of table
        //        strDim = strDim + " SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('" + ds.Tables[0].Rows[0]["TABLE_NAME"];
        //        strDim = strDim + " ')";

        //        DataSet dsData = sqlConn.GetDataSetFromFactory(strDim);
        //        DataTable dtnew = dsData.Tables[0];
        //        DataTable dtrem = dsData.Tables[1];
        //        DataTable dtColnm = dsData.Tables[2];

        //        //new data
        //        List<object> rows = new List<object>();
        //        foreach (DataRow dr in dtnew.Rows)
        //        {

        //            Dictionary<string, object> row = new Dictionary<string, object>();
        //            foreach (DataRowView drcol in DvCol)
        //            {
        //                row.Add(drcol["FIELD_NAME"].ToString(), dr[drcol["FIELD_NAME"].ToString()]);
        //            }

        //            rows.Add(row);
        //        }

        //        newgriddata.datarow = rows;

        //        //remove data
        //        List<object> remRows = new List<object>();
        //        foreach (DataRow dr in dtrem.Rows)
        //        {

        //            Dictionary<string, object> row = new Dictionary<string, object>();
        //            //foreach (DataRow drcol in dtColnm.Rows)
        //            foreach (DataRowView drcol in DvCol)
        //            {
        //                row.Add(drcol["FIELD_NAME"].ToString(), dr[drcol["FIELD_NAME"].ToString()]);
        //            }

        //            remRows.Add(row);
        //        }

        //        int index = 0;
        //        List<colModel> listColmodel = new List<colModel>();
        //        List<string> columns = new List<string>();

        //        if (bOnlydata == false)
        //        {

        //            //DvCol.RowFilter = " IS_PRIMARY =FALSE";

        //            foreach (DataRowView dr in DvCol)
        //            //foreach (DataRow dr in dtColnm.Rows)
        //            {

        //                //for columns
        //                columns.Add(dr["DISPLAY"].ToString());
        //                //for colmodel
        //                colModel colmodel = new colModel();

        //                colmodel.index = dr["FIELD_NAME"].ToString();
        //                colmodel.name = dr["FIELD_NAME"].ToString();

        //                Dictionary<string, object> edoptions = new Dictionary<string, object>();
        //                Dictionary<string, object> editrules = new Dictionary<string, object>();
        //                Dictionary<string, object> formatoptions = new Dictionary<string, object>();

        //                if (dr["DATA_TYPE"].ToString().ToLower() == "number") //integer for whole number
        //                {
        //                    colmodel.sorttype = "int";
        //                    colmodel.align = "right";
        //                    editrules.Add("number", true);
        //                    edoptions.Add("NullIfEmpty", true);

        //                    //formatter 
        //                    colmodel.formatter = "number";
        //                    //format option for number
        //                    formatoptions.Add("decimalSeparator", ".");
        //                    formatoptions.Add("defaulValue", 0);
        //                    formatoptions.Add("decimalPlaces", 2);
        //                }
        //                else if (dr["DATA_TYPE"].ToString().ToLower() == "varchar")
        //                {
        //                    colmodel.sorttype = "string";
        //                    colmodel.align = "left";
        //                    //formatter                           
        //                    colmodel.formatter = "defaultFormat";
        //                }
        //                else if (dr["DATA_TYPE"].ToString().ToLower() == "date")
        //                {
        //                    colmodel.sorttype = "date";
        //                    colmodel.align = "right";
        //                    editrules.Add("date", true);
        //                    colmodel.formatter = "date";
        //                    ////date format in mm/dd/yyyy
        //                    formatoptions.Add("srcformat", "ISO8601Long");
        //                    formatoptions.Add("newformat", "m/d/Y");
        //                }
        //                else if (dr["DATA_TYPE"].ToString().ToLower() == "float")
        //                {
        //                    colmodel.sorttype = "float";
        //                    colmodel.align = "right";
        //                }

        //                //add key to col model
        //                if (dr["IS_PRIMARY"].ToString().ToLower() == "true")
        //                {
        //                    colmodel.key = true;
        //                }

        //                listColmodel.Add(colmodel);
        //                index++;
        //            }
        //        }

        //        // int index = 0;
        //        DvCol.RowFilter = " IS_PRIMARY =TRUE";
        //        primarykey = DvCol[0]["FIELD_NAME"].ToString();
        //        newgriddata.primarykey = primarykey;

        //        remgriddata.datarow = remRows;
        //        remgriddata.primarykey = primarykey;
        //        newgriddata.colModelList = listColmodel;
        //        newgriddata.columns = columns;

        //        rulesData.newDimGrid = newgriddata;
        //        rulesData.remDimGrid = remgriddata;

        //        JavaScriptSerializer js = new JavaScriptSerializer();
        //        return js.Serialize(rulesData);

        //        //return rulesData;

        //    }
        //    catch (Exception ex)
        //    {
        //        Util.WritErrorLog(ClsName, "GetRelDataAcRule", "GetRelDataAcRule", "", ex);
        //        throw ex;
        //    }
        //}

        public string SaveRule()
        {

            try
            {

                Util.WritInfoLog(ClsName, "SaveRule", "Start Creating Connection");
                Connection sqlConn = new Connection();
                Util.WritInfoLog(ClsName, "SaveRule", "End Creating Connection");

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@HierarchyId", DbType.Int32, ParameterDirection.Input, 0, HierarchyId));
                lstparam.Add(new DBParameter("@VersionId", DbType.Int32, ParameterDirection.Input, 0, VersionId));
                lstparam.Add(new DBParameter("@NodeId", DbType.Int32, ParameterDirection.Input, 0, NodeId));
                lstparam.Add(new DBParameter("@dimname", DbType.String, ParameterDirection.Input, 255, dimname));
                lstparam.Add(new DBParameter("@txtDate", DbType.DateTime, ParameterDirection.Input, 255, txtDate));
                lstparam.Add(new DBParameter("@query", DbType.String, ParameterDirection.Input, 1000, query));
                lstparam.Add(new DBParameter("@queryObj", DbType.String, ParameterDirection.Input,1000, queryObj));

                Util.WritInfoLog(ClsName, "SaveRule", "method for AddRel", "usp_SaveRule");
                Int32 res = sqlConn.SaveDataByFactoryByProc("usp_SaveRule", ref(lstparam));
                Util.WritInfoLog(ClsName, "SaveRule", "End method for SaveRule");

                return "true";

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "SaveRule", "SaveRule", "", ex);
                return "false";
                throw ex;
            }
        }


        //before SP
        //save rule
        //public string SaveRule()
        //{

        //    try
        //    {

        //        Util.WritInfoLog(ClsName, "SaveRule", "Start Creating Connection");
        //        Connection sqlConn = new Connection();
        //        Util.WritInfoLog(ClsName, "SaveRule", "End Creating Connection");

        //        //string strQry = " SELECT * FROM X_MASTER_LIST WHERE TABLE_ABBR= '" + dimname + "'";              

        //        string strQry = " SELECT X_HIER_MASTER_LIST_RULES.TABLE_NAME FROM X_HIER_MASTER_LIST_RULES ";
        //        strQry = strQry + " INNER JOIN X_MASTER_LIST ON ";
        //        strQry = strQry + " X_HIER_MASTER_LIST_RULES.MASTER_LIST_ID =X_MASTER_LIST.LIST_ID ";
        //        strQry = strQry + " WHERE HIERARCHY_ID= " + HierarchyId;
        //        strQry = strQry + " AND TABLE_ABBR= '" + dimname + "'";

        //        //get end date of that hier_id and ver_id  -1
        //        strQry = strQry + " SELECT END_DATE FROM X_HIER_VERSIONS ";
        //        strQry = strQry + " WHERE HIERARCHY_ID = " + HierarchyId;
        //        strQry = strQry + " AND VERSION_ID = " + VersionId;


        //        Util.WritInfoLog(ClsName, "SaveRule", "Query for Get details in SaveRule", string.Format("Query:{0}", strQry));
        //        DataSet ds = sqlConn.GetDataSetFromFactory(strQry);
        //        Util.WritInfoLog(ClsName, "SaveRule", "End Query for Get details in SaveRule");

        //        if (ds.Tables[1].Rows.Count > 0)
        //        {
        //            EndDate = Convert.ToDateTime(ds.Tables[1].Rows[0]["END_DATE"]);
        //        }

        //        string strRuleTable = string.Empty;
        //        if (ds.Tables[0].Rows.Count > 0)
        //        {
        //            strRuleTable = ds.Tables[0].Rows[0]["TABLE_NAME"].ToString();
        //        }

        //        string strRule = " SELECT ISNULL(MAX(RULE_ID)+ 1,1) RULE_ID FROM " + strRuleTable;

        //        strRule = strRule + "  SELECT RULE_ID FROM " + strRuleTable;
        //        strRule = strRule + " WHERE NODE_ID = " + NodeId;

        //        DataSet dsRule = sqlConn.GetDataSet(strRule);

        //        // JavaScriptSerializer js = new JavaScriptSerializer();
        //        // List<Columns> listCols = js.Deserialize<List<Columns>>(queryObj);


        //        Int32 ret;

        //        if (bCurrRule == false)
        //        {
        //            //insert rule
        //            string strInsert = "INSERT INTO " + strRuleTable; //ds.Tables[1].Rows[0]["NODE_ID"]
        //            strInsert = strInsert + "(RULE_ID,NODE_ID,CRITERIA,CRITERIA_OBJECT,START_DATE,END_DATE) VALUES ";
        //            strInsert = strInsert + "( " + dsRule.Tables[0].Rows[0]["RULE_ID"] + "," + NodeId + ",'" + query + "','" + queryObj;
        //            strInsert = strInsert + "','" + DateTime.Now + "','" + Convert.ToDateTime(EndDate).ToString("MM/dd/yyyy") + "'";
        //            strInsert = strInsert + ") ";

        //            Util.WritInfoLog(ClsName, "SaveRule", "Query for insert in SaveRule", string.Format("Query:{0}", strInsert));
        //            ret = sqlConn.SaveDataByFactory(strInsert);
        //            Util.WritInfoLog(ClsName, "SaveRule", "End Query for insert in SaveRule");

        //        }
        //        else
        //        {
        //            //update rule
        //            string strQryUpd = " UPDATE " + strRuleTable + " SET CRITERIA = '" + query + "',";
        //            strQryUpd = strQryUpd + " CRITERIA_OBJECT = '" + queryObj + "'";
        //            strQryUpd = strQryUpd + " WHERE NODE_ID = " + NodeId.ToString();

        //            Util.WritInfoLog(ClsName, "SaveRule", "Query for update in SaveRule", string.Format("Query:{0}", strQryUpd));
        //            ret = sqlConn.SaveDataByFactory(strQryUpd);
        //            Util.WritInfoLog(ClsName, "SaveRule", "End Query for update in SaveRule");

        //        }

        //        return "true";

        //    }
        //    catch (Exception ex)
        //    {
        //        Util.WritErrorLog(ClsName, "SaveRule", "SaveRule", "", ex);
        //        return "false";
        //        throw ex;
        //    }
        //}
    }

}
